How to get the values with the largest sum?

[Ngoc Diep]

Please find the enclosed file in this post.
I want to find Which fruit has highest revenue in the 1st quarter of 2011.

First, I used the array formula to calculate the maximum units of fruit sold in the 1st quarter.
=MAX((C4:C63)+(D4:D63)+(E4:E63))
But I don't know how to combine Vlookup and Product function to match the result from the MAX function above with the price for calculating the revenue.

Second, I used a function to find the fruit name which has the highest revenue in the 1st quarter of 2011 (in this stage, I assumed that I had already done the first stage).

=IF(SUM(C4:E4)=$J$4,VLOOKUP(B4,'List of Fruits'!$B$4:$C$63,2,FALSE),0)

By copying down this function for the whole range, I got Bananas as a result for this question. My purpose was to just got the answer in 1 cell rather than in the whole column, how can I do it?

Many thanks to all you guys, please take a second to help me.

 

[NARAYANK991]

Hi ,

I am not sure that you have been asked to use the VLOOKUP function or the PRODUCT function.

The basic method is to get the individual totals for each fruit , for the first quarter. This is achieved by the following formula :

=SUBTOTAL(9,OFFSET(C4,ROW(C4:C63)-MIN(ROW(C4:C63)),,,3))

What this does is return an array of the first quarter totals for each fruit. Finding out what is the maximum in this array is done by using the MAX function around the above , as in :

=MAX(SUBTOTAL(9,OFFSET(C4,ROW(C4:C63)-MIN(ROW(C4:C63)),,,3)))

Since the list of fruits as well as the fruit sold figures are having all of the fruits listed in the same order , there is really no need to retrieve the fruit ID , since the position of the maximum can be used directly to index the names of the fruits , as in :

=INDEX('List of Fruits'!$C$4:$C$63,MATCH(MAX(SUBTOTAL(9,OFFSET($C$4,ROW($C$4:$C$63)-MIN(ROW($C$4:$C$63)),,,3))),SUBTOTAL(9,OFFSET($C$4,ROW($C$4:$C$63)-MIN(ROW($C$4:$C$63)),,,3)),0))

All of the above formulae are array formulae , to be entered using CTRL SHIFT ENTER.

Narayan

 

[Sajan]

Hello,
Here is one more approach to get the ID of the fruit with the highest Q1 revenue:
=LOOKUP(1, 1/FREQUENCY(-(9^9), -MMULT($C$4:$H$63,TRANSPOSE(--(MONTH($C$3:$H$3)<=3)))*('Unit Price'!$C$4:$C$63)), $B$4:$B$63)

enter with Ctrl + Shift + Enter

Cheers,
Sajan.

 

[Ngoc Diep]

Many thanks for your instructions. It works exactly what I mean.

 

[Ngoc Diep]

@NARAYANK991 How about calculate the revenue ( the sold amount * the unit price )? I sitll get stuck with it. Because your advice is on finding the fruit having the highest sold unit, not the hightset revenue. Hope you get what I mean. Thank you.

 

[NARAYANK991]

Hi ,

My mistake ; you will have to multiply the number of units sold by the unit price , as in :

=INDEX('List of Fruits'!$C$4:$C$63,MATCH(MAX(SUBTOTAL(9,OFFSET($C$4,ROW($C$4:$C$63)-MIN(ROW($C$4:$C$63)),,,3))*'Unit Price'!$C$4:$C$63),'Unit Price'!$C$4:$C$63*SUBTOTAL(9,OFFSET($C$4,ROW($C$4:$C$63)-MIN(ROW($C$4:$C$63)),,,3)),0))

Narayan

 

[Ngoc Diep]

Many thank you to you, NARAY YANK991