Excel formula, dollar amount into how many bills needed 1

[dbuffer]

I need help getting excel to take the new amount and in the four columns split that amount into dollars needed it could say 4 or 400 or 400.00 that doesn't matter. I am not that good at formulas like this. below is an example of my worksheet. When I post this the columns don't line up.

PrevBonus Add New 100's 50's 20's 5's needed
25.00 25.00 50.00 0 1 0 0
125.00 25.00 150.00 1 0 1 1
475.00 25.00 500.00 4 1 1 1
150.00 25.00 175.00 1 1 0 0
50.00 25.00 75.00 0 1 0 0
175.00 25.00 200.00 1 1 1 1
75.00 25.00 100.00 0 1 1 1
25.00 25.00 50.00 0 0 1 1
375.00 25.00 400.00 3 1 1 1

Total Bills 10 7 17 6


new$Formula=Prev+add Totals Bills for 100/50/20/05 are sum formulas

 

[AnotherHiggins]

You example isn't consistent: the first row counts how many to equal 'New', all the others count how many to equal 'PrevBonus'.

And you don't state this explicitly - so just to make sure - you want the smallest number of bills possible, right?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[dbuffer]

John,
Sorry if I wasn't good at explaining this. Let me try again. These are employee bonus' and I have the first column that is the previous bonus of say 450.00 and the amount added for the new year is another 25.00 so my new bonus total is 475.00 dollars. What I want to be able to do is have a formula that will take that 475.00 dollars and tell me how many 100 dollar bills I need, how many 50 dollar bills I need, how many 20 dollar bills I need, and how many 5 dollar bills I need. THe post I put on the site doesn't have any formulas in anything other than adding the previous bonus to the added bonus which would be the total of the new bonus.And then once there is a total in the dollar bills needed columns I just summed them. the numbers i put under the 100/50/20/5 I just typed in for an example there are no formula in them. Again, Sorry I didn't explain this better.

Debbie

 

[AnotherHiggins]

No problem. But if you look at your example data, all but the first row add up to the PrevBonus, not the New bonus.

These formulae will work for you:

100's (in column D):
[COLOR=blue white]=int(c2/100)[/color blue white]

50's(in column E):
[COLOR=blue white]=int((c2-(d2*100))/50)[/color blue white]

20's (in column F):
[COLOR=blue white]=int((c2-(d2*100)-(e2*50))/20)[/color blue white]

5's (in column G):
[COLOR=blue white]=int((c2-(d2*100)-(e2*50)-(f2*20))/5)[/color blue white]

To calculate for PrevBonus instead, you would just change the C2 to A2.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[dbuffer]

John,
Thank you very much I will try this. I now understand what you mean but the rows not adding up, I think. I wasn't using the new total to split the dollars out. Where 150 is I should have had 1 100.00 and 1 50.00 bills. Sorry about that, when I did it I was using the wrong column to split the amounts out. I did not want to use the previous bonus to split out the dollars I wanted to use the new bonus amount. It would help If my example was correct when I asked the question. Maybe I should change my name from technicaluser to beginneruser. Thank you again
Debbie

 

[AnotherHiggins]

Heh. Don't worry about it. We all make mistakes. I was just a little confused, that's all.



[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[dbuffer]

John,

It works wonderful!!!! Thank You

Debbie