If your numbers are in range A1:A100, use below formula =SUMPRODUCT((A1:A100)*(MOD(ROW(A1:A100)-ROW(A1)+1,2)=0)) Above formula is for every 2nd row. Replace 2 with N. Hence, for every 5th row – =SUMPRODUCT((A1:A100)*(MOD(ROW(A1:A100)-ROW(A1)+1,5)=0)) This is a generic formula and will work for any range. If you range is B7:B50, your formula would become =SUMPRODUCT((B7:B50)*(MOD(ROW(B7:B50)-ROW(B7)+1,2)=0))
Excel Quiz 5
Tips & Tricks 71 – Sum Every Odd Row
If your range is A1:A100, use following formula. =SUMPRODUCT((A1:A100)*(MOD(ROW(A1:A100)-ROW(A1)+1,2)<>0)) This is a generic formula, hence if your range is B7:B50, your formula will become =SUMPRODUCT((B7:B50)*(MOD(ROW(B7:B50)-ROW(B7)+1,2)<>0))
Tips & Tricks 70 – Sum Every Even Row
If your range is A1:A100, use following formula. =SUMPRODUCT((A1:A100)*(MOD(ROW(A1:A100)-ROW(A1)+1,2)=0)) This is a generic formula, hence if your range is B7:B50, your formula will become =SUMPRODUCT((B7:B50)*(MOD(ROW(B7:B50)-ROW(B7)+1,2)=0))
Excel Quiz 4
Tips & Tricks 69 – Sum Top N values in a Range
Suppose you have numbers in range A1:A100 and you want to sum up top N values =SUMPRODUCT(LARGE($A$1:$A$100,ROW(1:10))) In case, you want to ignore 0 values (and blanks) =SUMPRODUCT(LARGE(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…
Challenge 6 – Non-array Formula for Sum of Square of the Digits in a Numeric String
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…