Mon 13 Jul 2015

Solution - Challenge 9 – Get the Customer Spending Maximum Amount for a given Month and Starting Alphabet

By |Monday, July 13th, 2015|Categories: Solutions|Tags: , , , , , |1 Comment

Below is a possible solution for the challenge - Challenge 9 – Get the Customer Spending Maximum Amount for a given Month and Starting Alphabet

Put following Array formula in H4

=INDEX(D2:D20,MATCH(1,--(MAX(IF(C2:C20=TEXT(H2&"/1","mmm"),IF(LEFT(D2:D20,1)=H3,E2:E20)))
=(IF(C2:C20=TEXT(H2&"/1","mmm"),IF(LEFT(D2:D20,1)=H3,E2:E20)))),0))

Put following Array formula in H5

=INDEX(E2:E20,MATCH(1,--(MAX(IF(C2:C20=TEXT(H2&"/1","mmm"),IF(LEFT(D2:D20,1)=H3,E2:E20)))
=(IF(C2:C20=TEXT(H2&"/1","mmm"),IF(LEFT(D2:D20,1)=H3,E2:E20)))),0))

Note - Array Formula is not entered by pressing ENTER after entering your formula but by pressing CTRL+SHIFT+ENTER. If you are copying and pasting this formula, take F2 after pasting and CTRL+SHIFT+ENTER. This will put { } brackets around the formula which you can see in Formula Bar. If you edit again, you will have to do CTRL+SHIFT+ENTER again. Don't put { } manually.

The solution sheet can be downloaded from here - Solution-Challenge-9-–-Get-the-Customer-Spending-Maximum-Amount-for-a-given-Month-and-Starting-Alphabet

Sat 14 Feb 2015

Challenge 9 - Get the Customer Spending Maximum Amount for a given Month and Starting Alphabet

By |Saturday, February 14th, 2015|Categories: Challenges|Tags: , |1 Comment

Given the below table, the challenge before you is to - Work out a formula for Customer spending Maximum amount and his Spend (Cells H4 and H5) for a given Month (H2) and starting alphabet (H3).

Cell H2 has the month in numbers 1 to 12 and H3 has starting alphabet.

If H2 is blank, then it should search irrespective of the month.
If H3 is blank, it should search irrespective of the starting alphabet.
Example - For the month of Mar and starting Alphabet M, Maddy has spent the highest amount and his spend is 345.

The relevant Excel file can be downloaded from here Customer Max Spend.

Note - If you wish, you may post your answer in Comments.

1

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 26 Jul 2014

Tips & Tricks 35 - Highlight Cells which are different from first Column Cells in a Range (And for Row Cells also)

By |Saturday, July 26th, 2014|Categories: Tips and Tricks|Tags: , , , |0 Comments

Let's say you are having data as given below and you want to highlight all the cells in columns after first column where cell values are different.

Confused???

B1 is not different from A1 where C1 is different from A1, hence C1 will be highlighted.
In row 2 both B2 and C2 will highlighted and so on.

1

Approach

1. Select the required range. In above case, select A1:C8.

2. ALT+EGSW and Enter (ALT+EGS opens Go to Special and W selects "Row Difference")

And you will have your results.

1

ALT+EGS to disply

Note - For ROW CELLS, select Column Difference i.e. ALT+EGSM and you get following results (I have changed data set to illustrate the concept. Here comparison is done with first row for following rows -

1