PDA

View Full Version : Summing largest amounts - Formula limitations?



Simon Lloyd
01-29-2007, 11:49 AM
Hi all, i have just given a solution to an Ops who asked how do they calculate the top 7 figures in a row of 10, my solution was an array formula {=SUM(LARGE(A3:L3,{1,2,3,4,5,6,7}))}, what i would like to know is, are there any limitations to this formula?, does it only work on contiguous data? can it work over many rows?, does a blank count as a figure in this kind of formula? if any of the above have a negative effect is there a way to combat them?

Regards,
Simon

lucas
01-29-2007, 12:12 PM
I will let some of the guys that are better with formula's answer your questions but here is an alternative using rank within vba. the formula can also be used in the sheet if you choose to. This one looks for the 3 largest numbers......configurable.

Simon Lloyd
01-29-2007, 12:22 PM
Cheers Lucas, the thread i answered is located here http://www.excelforum.com/showthread.php?p=1746716&posted=1#post1746716 and a fellow called DaddyLongLegs gave this solution {=SUM(LARGE(A3:L3,ROW(INDIRECT("1:"&MIN(7,COUNT(A3: L3))))))}
as my solution would give an error if the range contained less than 7 values (and sure enough it does!) any other suggestions or help is appreciated - and if you could explain this portion of the formula
ROW(INDIRECT("1:"&MIN(7,COUNT(A3: L3))))))
because thsi ("1:"&MIN seems to be looking for a reference text?

Regards,
Simon

Bob Phillips
01-29-2007, 02:55 PM
Simon,

It isn't looking for text.

MIN(7,COUNT(A3: L3)

is the easy bit, that just ensures that you only pickup the number of numeric values within A3:L3, upto a maximum of 7

"1:"&MIN(7,COUNT(A3: L3)

then builds up a string of 1:that number, and

INDIRECT("1:"&MIN(7,COUNT(A3: L3))

indirects into those cells. Although "1:3" say might look like text, Excel will interpret that as a valid range. The ROW function

ROW(INDIRECT("1:"&MIN(7,COUNT(A3: L3)))

is then used to get an array of rows covered by the range that is being indirected into.

This seems like a lot of work, but it has two advantages,
firstly, you can add logic to ensure that, unlike yours, it does not exceed the number of numeric values in the target range, and
secondly, it can be used, again using added logice, where the number to evaluate is not known in advance.

Simon Lloyd
01-30-2007, 03:42 AM
Good to hear from you Bob, nicely explained!, as you see i am still trying to follow the "fix it with formulae first" route, whilst devising and understanding complex formulae is difficult (well it seems complex to me!) the end product seems to be a lot more efficient than VBA.

Regards,
Ever Learning Pupil

Bob Phillips
01-30-2007, 04:15 AM
Hi Simon,

You are doing the right thing IMO.

In most instances, formulae and built-in functionality will out-perform any VBA that we can cut. But, being human (well, most of us), we have a habit of not being thorough when we add, don't revisit the design etc., and the worksheet gets too bogged down. VBA can then often be a saviour, as we can do once what the spreadsheet might be doing 1,000 times.

BTW, I didn't mention in my earlier response (but you probably understood this anyway), that the whole point of that part of the formula was to build a constants array, similar to your {1,2,3,4,5,6,7}.