i'm looking for a domination breakdown calclator

[Guest]

Hi i was wondering if anyone can help me with a template that calulates what
dominations of cash breakdown i need for a payroll amount. i have each amount
each employee will receive but i have to do it manually. a template would be
a great help.

thanks

 

[Guest]

If you only need to break down denominations of paper money, try this:

With
A2: (a numeric value)

B1: 20's
C1: 10's
D1: 5's
E1: 1's

B2: =INT(A2/20)
C2: =INT(MOD(A2,20)/10)
D2: =INT(MOD(A2,10)/5)
E2: =INT(MOD(A2,5)/1)

Copy those formulas down as far as you need.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Roger Govier]

Hi Bec

Depending upon the country you are in you may have to change the values
you enter in row 1 of your table.
For the UK I set up as follows
In cells B1:M1
50, 20, 10, 5, 2, 1, 0.50, 0.20, 0.10, 0.05, 0.02, 0.01
In cell A2 I entered the first salary
in cell B2
=IF($A2="","",INT($A2/B$1))
in cell C2
=IF($A2="","",INT(($A2-SUMPRODUCT(($B2:B2)*($B1:B1)))/C$1))
Copy C2 across through D2:M2
Copy B2:M2 down the page as far as you require

 

[SteveW]

Neat, works for row 2, but needs a tweak/edit for rows 3 onwards

It needs $1's in the $B1:B1
ie =IF($A2="","",INT(($A2-SUMPRODUCT(($B2:B2)*($B$1:B$1)))/C$1))

Steve

 

[Gord Dibben]

Geez

I saw the "domination" and thought this will be an interesting post.

Got me all excited<g>


Gord Dibben MS Excel MVP

 

[Roger Govier]

Hi Steve

Thanks for picking up the error.
I had realised if after going to bed last night,
(even though I live on a farm, I don't count sheep to go to sleep -
Excel puzzles do just fine <vbg>)
and wasn't going to do anything until getting up this morning.
I felt pretty sure somebody would have picked it up by then though.

--
Regards

Roger Govier


Neat, works for row 2, but needs a tweak/edit for rows 3 onwards

It needs $1's in the $B1:B1
ie =IF($A2="","",INT(($A2-SUMPRODUCT(($B2:B2)*($B$1:B$1)))/C$1))

Steve