Let's say cell A1 contains a numeric string. The challenge before you is to give me a non-array formula to sum the square of the digits. As an example if A1=378906, then answer would be = 9+49+64+81+0+36 = 239 Note – You may choose to post the response in comments section.
Challenge 5 – Prepare a Graphical Chart
This time, it is a challenge to make a chart like below. The relevant data points and picture are in the Excel file which can be download from here. Tiger Count Note – You may choose to post the response in comments section.
Tips & Tricks 68 – Sum Bottom N Values in a Range
Suppose you have numbers in range A1:A100 and you want to sum up bottom N values =SUMPRODUCT(SMALL($A$1:$A$100,ROW(1:10))) In case, you want to ignore 0 values (and blanks) =SUMPRODUCT(SMALL(IF($A$1:$A$100<>0,$A$1:$A$100),ROW(1:10))) Both the above formulas will function only if there are at least N values as per ROW(1:N). Hence, for above formulas, it would work only if there…
Tips & Tricks 67 – Show the Complete Data in Chart even though Data is Filtered
By default, charts in excel show filtered data only. To show complete data i.e. to ignore filtering – 1. Right click on Chart and click on Select Data. 2. Click on Hidden and Empty Cells at the bottom 3. Select Show data in hidden rows and columns. Now, you will have the result which you…
Excel Quiz 3
Article 8 – Calculating Percentage Change between New and Old Value
Many times, I get inspiration to write about articles when I post responses to questions on Microsoft Community. The inspiration to write about this has come from following post – Percentages You will also have many occasions particularly when you are asked to compute percentage change between two values. This is very much encountered in…
Challenge 4 – SUM of Multiplication of Preceding Digits by Succeeding Digits
Let's say you have a number 64934 in a cell A1. Now, the challenge before you is to work out the following SUM through a formula which should be flexible enough to capture any number of digits (Don't limit this to 15 digits maximum as allowed by Excel precision but to any number of digits…
Tips & Tricks 66 – Show the Complete Area of a Worksheet as only Limited Area is being shown
If you can see only limited area of a worksheet 1. Check if the sheet is protected. This you check by going to Review tab and see if Unprotect Sheet Button is appearing. – If Unprotect Sheet Button is appearing sheet is protected. You need to click it and if password is asked, you need…
Tips & Tricks 65 – Show a Limited Area of Worksheet
You have a worksheet and you want to show only a limited area to users. Say you want them to see only A1:G50 and remaining you don't want to show them. Use following steps – 1. Click column H so that entire column is selected. Now CTRL+SHIFT+Right Arrow key so that all columns will get…