Microsoft Excel - A summary formula for denomination/coinage analysis

Hi all excel experts,
I have data in this format and i want to pay cash to 300 casual workers so i need the denomination breakdown:

Name   Date:      Amount
   A    31-05-13   120,550
   B    30-06-13     65,700
   C    31-05-13   1,250,700

On cell A5 is date criteria 31-05-13
On cell B6 to B10 I fill in the denomination needed and B6:B10 the formula is needed
10,000 =DENOMINATION((B2:B4=A5)*(B2:B4,B6)), how many 10,000s are needed in May 31st
  5,000 =DENOMINATION((B2:B4=A5)*(B2:B4,B7)), how many 5,000s are needed in May 31st
  2,000 =DENOMINATION((B2:B4=A5)*(B2:B4,B8)), how many 2,000s are needed in May 31st
  1,000 =DENOMINATION((B2:4=A5)*(B2:B4,B9)), how many 1,000s are needed in May 31st
    500 =DENOMINATION((A2:A4=A5)*(B2:B4,B10)), how many 500s are needed in May 31st

Thank you in advance!
Gauss.

Hello gauss,
What exactly is the amount?  If it is the total payroll, then you do not have enough information to do what you want.  You have to have each individuals amount and calculate the coinage breakdown for each and then total each breakdown to get the overall requirement.
I don't quite understand your layout compared to your description.  I would put in a helper column (I have used A - A6 to A10 et seq) and put in that your coinage denominations.  Your columnB then becomes columnC.  Your amount is in cell C5.  In cell C6 put this formula =(C$5-MOD(C$5,$A6))/$A6
In cell C7 put this formula =(C$5-SUMPRODUCT($A$6:$A6,C$6:C6)-MOD(C$5-SUMPRODUCT($A$6:$A6,C$6:C6),$A7))/$A7
Copy this down to C10 (or as far as you need to the smaller denominations to get an exact balance).  The formulae can also be copied across columns.
Regards
Harry

Harry,

I was working on something vary similar but I was using Mod and rounddown I never thought of using -mod to work the problem that works better than trying to make each cell dependent on the one above for the remainder.

I learned something today thanks


Don