Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    New Lounger
    Join Date
    Mar 2002
    Location
    Texas, USA
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Automatically Caculate Bills Needed (2000,SR1)

    Column A of my spreadsheet contains the names of award recipients, Column B shows the amount of award. The amount per person can range from $50 to $250, in $25 increments.

    Each month I have to manually calculate the number of $5, $20, $50 and $100 bills I need to get from the bank. I'm hoping that someone can give me some direction as to how this could be automatically done in Excel.

    Thanks,
    Elaine

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Automatically Caculate Bills Needed (2000,SR1)

    You could use something like these formulas:

    <table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center valign=bottom>1</td><td align=right valign=bottom>15346.37</td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=center valign=bottom>2</td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=center valign=bottom>3</td><td align=right valign=bottom>100</td><td align=right valign=bottom>153</td><td valign=bottom>=INT($A$1/A3)</td><td align=center valign=bottom>4</td><td align=right valign=bottom>50</td><td align=right valign=bottom>0</td><td valign=bottom>=INT(($A$1-SUMPRODUCT($A$3:A3,$B$3:B3))/A4)</td><td align=center valign=bottom>5</td><td align=right valign=bottom>20</td><td align=right valign=bottom>2</td><td valign=bottom>=INT(($A$1-SUMPRODUCT($A$3:A4,$B$3:B4))/A5)</td><td align=center valign=bottom>6</td><td align=right valign=bottom>10</td><td align=right valign=bottom>0</td><td valign=bottom>=INT(($A$1-SUMPRODUCT($A$3:A5,$B$3:B5))/A6)</td><td align=center valign=bottom>7</td><td align=right valign=bottom>5</td><td align=right valign=bottom>1</td><td valign=bottom>=INT(($A$1-SUMPRODUCT($A$3:A6,$B$3:B6))/A7)</td><td align=center valign=bottom>8</td><td align=right valign=bottom>1</td><td align=right valign=bottom>1</td><td valign=bottom>=INT(($A$1-SUMPRODUCT($A$3:A7,$B$3:B7))/A8)</td><td align=center valign=bottom>9</td><td align=right valign=bottom>0.25</td><td align=right valign=bottom>1</td><td valign=bottom>=INT(($A$1-SUMPRODUCT($A$3:A8,$B$3:B8))/A9)</td><td align=center valign=bottom>10</td><td align=right valign=bottom>0.1</td><td align=right valign=bottom>1</td><td valign=bottom>=INT(($A$1-SUMPRODUCT($A$3:A9,$B$3:B9))/A10)</td><td align=center valign=bottom>11</td><td align=right valign=bottom>0.05</td><td align=right valign=bottom>0</td><td valign=bottom>=INT(($A$1-SUMPRODUCT($A$3:A10,$B$3:B10))/A11)</td><td align=center valign=bottom>12</td><td align=right valign=bottom>0.01</td><td align=right valign=bottom>2</td><td valign=bottom>=INT(($A$1-SUMPRODUCT($A$3:A11,$B$3:B11))/A12)</td></table>

    Calculate for each person and then sum them up. I know you shouldn't get change, but I just did it for completeness.
    Steve

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automatically Caculate Bills Needed (2000,SR1)

    Here is a different (less general) approach.

  4. #4
    New Lounger
    Join Date
    Mar 2002
    Location
    Texas, USA
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically Caculate Bills Needed (2000,SR1)

    Thanks for the quick replies guys.

    Since Hans was more specific to what I need, I will use his method. I've been manually calculating these each month for 12 years so you have made me very happy!

    Thanks,
    Elaine

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Automatically Caculate Bills Needed (2000,SR1)

    Hans,

    Your approach inspired me to learn something new ala a slightly different approach in the attached. Thanks. Not sure if the attached approach would run any faster in a large workbook. Opinion?

    Explanation is strictly for me bcs there's no way I'd remember this the next time around.

    Fred

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automatically Caculate Bills Needed (2000,SR1)

    Fred,

    Your alternative formula works fine. It might be slightly more efficient, since using array formulas results in fewer formulas. But my solution could be changed to use array formulas too.

    Steve's solution doesn't use lookup formulas at all, but is more complicated, so I don't know which one would be best for large worksheets.

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Automatically Caculate Bills Needed (2000,SR1)

    Hans,

    Thanks. I was wondering how to use VLOOKUP in an array formula. I tried switching the 3rd arg (the col to return) to
    - ROWS(2:5) with an array formula and the 4 cells selected. ROWS(2:5) evaluated to 4 for all cells, so I got whatever was in col 4 of the table.

    How would one use VLOOKUP in an array formula in this case?

    Fred

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automatically Caculate Bills Needed (2000,SR1)

    With VLOOKUP, you might use array formulas by column instead of by row. If the number of rows is very large, that might be even more efficient, because there are in effect only 4 formulas. See attached version.

  9. #9
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Automatically Caculate Bills Needed (2000,SR1)

    Hans,

    Thanks for the info. Naturally that leads to another question:

    With some builtin functions, it may be obvious as to which argument can be used as an array. Others, it may not be so obvious. When you mentioned a few emails ago that you could use an array function with VLOOKUP, I tried it with the 3rd arg and got nowhere. The solution was to use it with the 1st arg instead.

    In general, how would one know which arguments can be used as an array?

    And another question:

    In my INDEX array solution, the use of the array was not for the purpose of passing multiple inputs to the built-in function. In fact, I had tried this solution originally by only selecting one cell and got one answer (expecting Excel to fill in the cells to the right, kind of like copying a set of cells but only selecting the "upper left" cell for the paste). In this case, the array formula was needed to get multiple outputs (even Excel's help mentioned this).

    In your VLOOKUP array solution, the use of the array was for both input and output purposes.

    How does one know which is needed?

    Fred

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Automatically Caculate Bills Needed (2000,SR1)

    You can use the rows as the array, inC15:
    <pre>{=VLOOKUP($B15,$B$2:$F$10,H$13:K$13,FALSE)} </pre>


    and then copy it to C16:C20, the number of formulas in this setup equals the number of people. In Hans' version it equals only 4: the number of different types of bills.

    If you want only 1 formula, you can modify your "index approach" to use columns and rows in an array.Put into C15:
    <pre>=INDEX($C$2:$F$10,MATCH($B$15:$B$20,B$2:$B$10 ,0),$H$22:$K$22)</pre>


    Then select c15:F20 and confirm with ctrl-shift-enter and this will fill it all in with 1 formula

    I am not clear what you are asking. You must make an array formula if you have a formula that does outputs an array (and excel does not explicitly handle the array in the function). There are some "array" functions in excel that do not require the ctrl-shift-enter (SUM, SUMPRODUCT, etc) unless the result is an array, they are not neccesary internally. Sometimes in formulas you don't output it to the range, but only output it within the formula.

    Steve

  11. #11
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Automatically Caculate Bills Needed (2000,SR1)

    Steve,

    Thanks for the input. I had a feeling one could use VLOOKUP for the rows but couldn't get it to work. But I see your point that Hans' VLOOKUP approach requires only 4 formulas - 1 per denomination - as opposed to n formulas - 1 per person. But does it make a (big) difference if the number of cells they cover are the same?

    You also wrote:
    >I am not clear what you are asking. You must make an array formula if you have a formula that does outputs an array (and excel does not explicitly handle the array in the function). There are some "array" functions in excel that do not require the ctrl-shift-enter (SUM, SUMPRODUCT, etc) unless the result is an array, they are not neccesary internally. Sometimes in formulas you don't output it to the range, but only output it within the formula.

    I couldn't quite phrase it properly. Is your terminology, ie, "output it to the range" vs "output it within the formula", the way to view the distinction I was driving at with builtin in functions used within array formulas? Let me give a few examples of what I was driving at:
    - {=SUM(IF((a1:a10>5)*(b1:b10="dog"),true,false))}: IF ordinarily only takes one condition but here must be created as an array formula. But the result (the SUM) goes into a single cell - perhaps bcs it returns only 1 value. But you hint that SUM might return an array also. Does that mean there are some uses of SUM as part of an array formula that would provide an array of >1 cell?
    - my {=INDEX(...MATCH...)} formulation to the original query in this thread outputs to a range, even per Help. So I could select one cell and only get the first element of the array answer w/o any error, even tho I entered this as an array formula. If I select 2 cells, I get the first 2 elements of the array, etc. In this case, I really do need to select an array for the "correct" answer. This would be an "output to the range", I guess.

    Fred

  12. #12
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Automatically Caculate Bills Needed (2000,SR1)

    Steve,

    I just tried to use your 2 suggestions (for horizontal VLOOKUP solution and for 1 formula for the whole thing). I'm not sure what the references to H$13:K$13 and $H$22:$K$22 mean. These were not part of my original INDEX approach (mine covered rows 15:20).

    Fred

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Automatically Caculate Bills Needed (2000,SR1)

    OOPS, Sorry, I forgot to mention and change these.

    This was because of my "playing" in a different range:
    In my "playing" H13:K13 had the numbers 2:5 (they could be added to C13:F13) and they are the column lookups in the VLOOKUP formula

    Similarly H22:K22 had the numbers 1-4 for the index. If you go with the INDEX these could go into C13:F13.

    Though on further reflection, this will also work and not require the numbers in the cells:
    <pre>=INDEX($C$2:$F$10,MATCH($B$15:$B$20,B$2:$B$10 ,0),COLUMN(INDIRECT("1:5")))</pre>


    I had setup the formulas in different columns to be able to compare to the original.

    <hr>But does it make a (big) difference if the number of cells they cover are the same?<hr>
    I don't know, on some level I can see how 1 formula is better than many, but without the arrays...
    It would only matter, if you had a large workbook and did many, many calcs. If you don't notice any "sluggishness" in the calcs, I wouldn't worry about it. If you get to that situation, set it up multiple ways and see how long to calc.

    <hr>Does that mean there are some uses of SUM as part of an array formula that would provide an array of >1 cell?<hr>
    You can use SUM "within" a formula and have the result be an array. If SUM is the Initial function, it will be a single cell result, but you could use the SUM as an intermediate calc.

    <hr>So I could select one cell and only get the first element of the array answer w/o any error, even tho I entered this as an array formula. If I select 2 cells, I get the first 2 elements of the array, etc. In this case, I really do need to select an array for the "correct" answer<hr>
    Yes, if the formula "returns" an array, it will only give the results of the number of cells you choose:
    If the answer is 1 cell and you "output" to more than 1 cell, all will have the number
    If the answer is a column of numbers and you choose 1 cell, you get first result, 2 cells, first 2 etc. If you select the number of rows: all the results. If you select additional columns for output, you will get a duplicate column.
    Likewise if your result is a row array and you select multiple rows, you get a duplicate
    If your answer is a 2-dimensional array, if you select less columns or less rows than the result, you will get incomplete answer. If you select more, you will "errors" in the cell outside the answers.

    Steve

  14. #14
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Automatically Caculate Bills Needed (2000,SR1)

    Steve,

    can you post your solutions (sounds like you have 2 similar ones - 1 with and the other without col headings) if you still have them. I'm having some difficulty visualizing exactly what you did.

    I had also set up my original INDEX(..MATCH..)) solution in diff columns alongside Hans' post to see how they compare.

    Thanks.

    Fred

  15. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Automatically Caculate Bills Needed (2000,SR1)

    I will walk you thru it, you will learn better that way.
    Take the file you originally posted
    Select the cells C15:F20 <delete>
    With the range still selected,
    copy this line of code from here at WOPR
    =INDEX($C$2:$F$10,MATCH($B$15:$B$20,B$2:$B$10,0),C OLUMN(INDIRECT("1:5")))
    and paste it into the formula bar in Excel
    Now confirm with ctrl-shift-enter

    The entire range will be filled from this one array formula.

    Steve

Page 1 of 2 12 LastLast

Posting Permissions

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