Consulting

Results 1 to 6 of 6

Thread: Summing largest amounts - Formula limitations?

  1. #1
    Moderator VBAX Guru Simon Lloyd's Avatar
    Joined
    Sep 2005
    Location
    UK
    Posts
    3,003
    Location

    Summing largest amounts - Formula limitations?

    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 [VBA]{=SUM(LARGE(A3:L3,{1,2,3,4,5,6,7}))}[/VBA], 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
    Regards,
    Simon
    Please read this before cross posting!
    In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
    If I have seen further it is by standing on the shoulders of giants.
    Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)

  2. #2
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    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.
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  3. #3
    Moderator VBAX Guru Simon Lloyd's Avatar
    Joined
    Sep 2005
    Location
    UK
    Posts
    3,003
    Location
    Cheers Lucas, the thread i answered is located here http://www.excelforum.com/showthread...=1#post1746716 and a fellow called DaddyLongLegs gave this solution [VBA]{=SUM(LARGE(A3:L3,ROW(INDIRECT("1:"&MIN(7,COUNT(A3: L3))))))}
    [/VBA]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
    Regards,
    Simon
    Please read this before cross posting!
    In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
    If I have seen further it is by standing on the shoulders of giants.
    Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,453
    Location
    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.
    Last edited by Bob Phillips; 01-30-2007 at 04:16 AM.

  5. #5
    Moderator VBAX Guru Simon Lloyd's Avatar
    Joined
    Sep 2005
    Location
    UK
    Posts
    3,003
    Location
    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
    Regards,
    Simon
    Please read this before cross posting!
    In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
    If I have seen further it is by standing on the shoulders of giants.
    Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,453
    Location
    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}.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •