Solved Total three largest values from a selection of cells Part 2

October 1, 2013 at 10:25:30
Specs: Windows 7
=SUM(LARGE(C20:F20:I20:L20,{1,2,3}))

This work the worksheet as required, however the selected cells may number up to 50
I have tried the following but know that I am missing something, but I can't see what

=SUM(LARGE((C20,F20,I20,L20),ROW(INDIRECT("1:C2"))))

C2 is a volatile number, subject to change given a specific value from elsewhere in the worksheet, so I could be looking for the total of the largest 2 values, or the total of the largest 12 values.

message edited by tonygibb


See More: Total three largest values from a selection of cells Part 2

Report •


✔ Best Answer
October 1, 2013 at 14:05:06
=IF($C$2=1,SUM(LARGE((C19,F19,I19,L19),{1})),IF($C$2=2,SUM(LARGE((C19,F19,I19,L19),{1,2})),IF($C$2=3,SUM(LARGE((C19,F19,I19,L19),{1,2,3})),IF($C$2=4,SUM(LARGE((C19,F19,I19,L19),{1,2,3})),0))))

Thank you so much for your reply, I understand your explanation, but the method of CTRL,Shift,Enter would tend for the end User to get confused.
My intention is that they are to only to create the value for C2 and that the calculations take place without any other key motion.
I have created the above nested IF function which will do what I require.
I just have to amend it slightly to accommodate the number of values to be Sum totalled

Again thanks for your help



#1
October 1, 2013 at 11:22:39
This first I see is a major difference in the ranges you are referencing.

This formula references 2 individual ranges: "C20:F20" and "I20:L20"

=SUM(LARGE(C20:F20:I20:L20,{1,2,3}))

In other words, it will sum the 3 largest values in C20, D20, E20, F20, I20, J20, K20, and L20.

This formula references 4 individual cells: C20, F20, I20 and L20.

=SUM(LARGE((C20,F20,I20,L20),ROW(INDIRECT("1:C2"))))

There's a big difference between using commas and using colons.

Before we go any further, can you explain why one formula uses colons and the other uses commas? The reason I ask is because you say that C2 could be from 2 to 12.

You can't get the 12 largest values from a range of only 4 cells.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
October 1, 2013 at 11:39:47
Sorry about that,

=SUM(LARGE(C20:F20:I20:L20,{1,2,3}))

should have read

=SUM(LARGE((C20,F20,I20,L20),{1,2,3}))

I have only included 4 selected cells but because of the variation in what I have, there may be up to 50 selected cells, so I have done this to keep it short

C2 is a value generated by another part of the worksheet. This will not exceed the number of selected cells, but will request the total of the largest values to that value.

=SUM(LARGE((C20,F20,I20,L20),ROW(INDIRECT("1:C2"))))

So what am I missing. I still cannot see what is probably a very simple answer.



Report •

#3
October 1, 2013 at 12:36:02
re: I still cannot see what is probably a very simple answer

Actually, the answer is not all that simple. In fact, I'm a bit confused myself, but I think I got it to work.

This formula seems to work when entered as an array formula with Ctrl-Shift-Enter:

For this explanation, assume there is a 3 in C2.

=SUM(LARGE((C20,F20,I20,L20),ROW(INDIRECT("1:"&C2)))) Ctrl-Shift-Enter

There were 2 issues with your formula:

1 - The INDIRECT function:

The reference to C2 has to be pulled out from the text string argument of the INDIRECT function in order for the actual value in the cell to be picked up by the ROW function.

INDIRECT("1:C2") will not work because "1:C2" is not a text string that INDIRECT can use as a reference. "1:" & C2 becomes 1:3 which works fine.

2 - The use of the array for the k argument of the LARGE function.

This part is interesting and I can't really explain why it is happening, but here is what I found.

If I use this formula, not entered as an array formula it only "SUMs" the single largest value. In other words, it is not using {1,2,3} as the k argument, it just uses the 1.

=SUM(LARGE((C20,F20,I20,L20),ROW(INDIRECT("1:"&C2))))

However, if I highlight ROW(INDIRECT("1:"&C2)) and press F9 (which evaluates that part of the formula directly in the formula bar) it returns {1;2;3} . Note the semi-colons, not commas. I then see this:

=SUM(LARGE((C20,F20,I20,L20),{1;2;3}))

If I hit Enter, this formula does indeed SUM all three values. What that tells me is that when the formula is not array entered, the result of ROW(INDIRECT("1:"&C2)), namely {1;2;3}, is not being picked up as an array. However, when Ctrl-Shift-Enter is used, it becomes an array for the SUM(LARGE...)) functions to use.

Interesting...

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
October 1, 2013 at 14:05:06
✔ Best Answer
=IF($C$2=1,SUM(LARGE((C19,F19,I19,L19),{1})),IF($C$2=2,SUM(LARGE((C19,F19,I19,L19),{1,2})),IF($C$2=3,SUM(LARGE((C19,F19,I19,L19),{1,2,3})),IF($C$2=4,SUM(LARGE((C19,F19,I19,L19),{1,2,3})),0))))

Thank you so much for your reply, I understand your explanation, but the method of CTRL,Shift,Enter would tend for the end User to get confused.
My intention is that they are to only to create the value for C2 and that the calculations take place without any other key motion.
I have created the above nested IF function which will do what I require.
I just have to amend it slightly to accommodate the number of values to be Sum totalled

Again thanks for your help


Report •

#5
October 1, 2013 at 16:02:22
I don't understand your response. What do the end users have to do with this?

The formula is array entered by whoever places the formula in the cell, one time. Just like you are entering any other formula except that instead of just hitting enter, you use Ctrl-Shift-Enter.

You have end-users entering the formulas?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question