Results 1 to 15 of 20

20040421, 13:04 #1
 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

20040421, 13:22 #2
 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$1SUMPRODUCT($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$1SUMPRODUCT($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$1SUMPRODUCT($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$1SUMPRODUCT($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$1SUMPRODUCT($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$1SUMPRODUCT($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$1SUMPRODUCT($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$1SUMPRODUCT($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$1SUMPRODUCT($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

20040421, 13:29 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Automatically Caculate Bills Needed (2000,SR1)
Here is a different (less general) approach.

20040421, 14:08 #4
 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

20040501, 18:04 #5
 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

20040501, 18:49 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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.

20040502, 00:04 #7
 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

20040502, 00:10 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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.

20040502, 11:45 #9
 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 builtin 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

20040502, 15:30 #10
 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 ctrlshiftenter 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 ctrlshiftenter (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

20040502, 21:37 #11
 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 ctrlshiftenter (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

20040502, 21:54 #12
 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

20040503, 08:36 #13
 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 14 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 2dimensional 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

20040503, 11:31 #14
 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

20040503, 12:29 #15
 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 ctrlshiftenter
The entire range will be filled from this one array formula.
Steve