making change?

[curtis2919]

I'm looking for some help and the closest thing I can equate to my problem is teaching someone to make change. I'm going to have a number pop up on a sheet like 88. I'd like to have told my sheet that the only bills available in my whacky world are in denominations of 32s, 16s, and 8s and that it must select from the highest bill possible instead of telling me 88 $1 dollar billers. So I would already have separate rows listing out 32s, 16s, and 8s and I'd like it to tell me (2) $32, (1) $16, (1) $58 and (0) leftover. Sometimes the starting number might be something like 92 and there's no way to get that with my made up money so i'd like to just throw the remainder of 4 into a final row that can be easily seen.

Help would be great, or giving me some keywords to search on this forum for a certain type of statement or formula would be equally helpful

[jwhitwell]

How about this...

Row 1 is 'headers':
B1 = 32
C1 = 16
D1 = 8
E1 = Remainder

Row 2 is data and formulas:

A1 = your input cell, or 92 in your example
B1 = =ROUND(A2/B1-0.5,0)
C1 = =ROUND((A2-(B2*B1))/C1-0.5,0)
D1 = =ROUND((A2-(B2*B1)-(C2*C1))/D1-0.5,0)
E1 = =(A2-(B2*B1)-(C2*C1)-(D2*D1))

That gives you 2 '32s', 1 '16', 1 '8', and a remainder of 4.

The "-0.5" in each ROUND calculation just forces it to round down to the nearest whole number.

Someone else might have a more elegant solution, but I think this works for your question.