Sat 27 Dec 2014

Tips & Tricks 71 – Sum Every Odd Row

By |Saturday, December 27th, 2014|Categories: Tips and Tricks|Tags: , , , |0 Comments

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))

Sat 20 Dec 2014

Tips & Tricks 70 – Sum Every Even Row

By |Saturday, December 20th, 2014|Categories: Tips and Tricks|Tags: , , |0 Comments

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))

Sat 20 Dec 2014

Excel Quiz 4

By |Saturday, December 20th, 2014|Categories: Quizzes|Tags: , , |0 Comments

The fourth quiz in the series to test your general knowledge in Excel. After you hit Finish Quiz, you can click on View Questions to compare your answers to correct answers.

Sat 13 Dec 2014

Tips & Tricks 69 – Sum Top N values in a Range

By |Saturday, December 13th, 2014|Categories: Tips and Tricks|Tags: , , , |0 Comments

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 are at least 10 numbers in A1 to A100.

To overcome this limitation -

Enter the below formulas as Array Formula

=SUM(IFERROR(LARGE($A$1:$A$100,ROW(1:10)),0))

=SUM(IFERROR(LARGE(IF($A$1:$A$100<>0,$A$1:$A$100),ROW(1:10)),0))

Non Array Versions of above formulas (For Excel 2010 and above)

=SUMPRODUCT(AGGREGATE(14,6,$A$1:$A$100,ROW(1:10)))

=SUMPRODUCT(AGGREGATE(14,6,$A$1:$A$100/($A$1:$A$100<>0),ROW(1:10)))

Sat 13 Dec 2014

Challenge 6 – Non-array Formula for Sum of Square of the Digits in a Numeric String

By |Saturday, December 13th, 2014|Categories: Challenges|Tags: , , , |2 Comments

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

1 = 9+49+64+81+0+36 = 239

Note - You may choose to post the response in comments section.

Sat 06 Dec 2014

Challenge 5 - Prepare a Graphical Chart

By |Saturday, December 06th, 2014|Categories: Challenges|Tags: , , , , |1 Comment

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

1

Note - You may choose to post the response in comments section.

Sat 06 Dec 2014

Tips & Tricks 68 – Sum Bottom N Values in a Range

By |Saturday, December 06th, 2014|Categories: Tips and Tricks|Tags: , , |0 Comments

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 are at least 10 numbers in A1 to A100.

To overcome this limitation -

Enter the below formulas as Array Formula

=SUM(IFERROR(SMALL($A$1:$A$100,ROW(1:10)),0))

=SUM(IFERROR(SMALL(IF($A$1:$A$100<>0,$A$1:$A$100),ROW(1:10)),0))

Non Array Versions of above formulas (For Excel 2010 and above)

=SUMPRODUCT(AGGREGATE(15,6,$A$1:$A$100,ROW(1:10)))

=SUMPRODUCT(AGGREGATE(15,6,$A$1:$A$100/($A$1:$A$100<>0),ROW(1:10)))