Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Need help with coding
I work for the company where we still pay the cash wages, before I go to the
bank I Have to work out what how many hundreds fifties and so on to get ,I set up on a spreadsheet where I put the amount earn for example $2085 dollars and at this stage I put manually how may hundreds ,fifties ,twenties ,tens and fives goes into that sum is there a code so it can automatically do this for me in excel 2003 Can anyone help Thank you in advance $100 $50 $20 $10 $5 $2,085 20 1 1 1 1 $1,975 19 1 1 1 $2,135 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Need help with coding
Hi anna
Taking A1 = amount ($2085) b1= 100, c1 = 50, d1 = 20, e1=10, f1 = 5 B2 (100's) = QUOTIENT(A1,100) c2 (50's) =FLOOR(($A$1-$B$2*$B$1)/C1,1) D2 (20's) =FLOOR(($A$1-($B$2*$B$1+$C$2*$C$1))/D1,1) E2 (10's)=FLOOR(($A$1-($B$2*$B$1+$C$2*$C$1+$D$2*$D$1))/E1,1) F2 (5's) =FLOOR(($A$1-($B$2*$B$1+$C$2*$C$1+$D$2*$D$1+$E$2*$E$1))/F1,1) If you want to extend the formula you have to add the other denominations. Best wishes Sreedhar "anna" wrote: I work for the company where we still pay the cash wages, before I go to the bank I Have to work out what how many hundreds fifties and so on to get ,I set up on a spreadsheet where I put the amount earn for example $2085 dollars and at this stage I put manually how may hundreds ,fifties ,twenties ,tens and fives goes into that sum is there a code so it can automatically do this for me in excel 2003 Can anyone help Thank you in advance $100 $50 $20 $10 $5 $2,085 20 1 1 1 1 $1,975 19 1 1 1 $2,135 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Need help with coding
One way. I have done this for a postage calculator.
$ 2,585.00 TOTAL Using Variables in 1st column 100.00 =INT(D17/B17) =B15 50.00 =INT(D18/B18) =ROUND(MOD(D17,B17),2) 20.00 =INT(D19/B19) =ROUND(MOD(D18,B18),2) 10.00 =INT(D20/B20) =ROUND(MOD(D19,B19),2) 5.00 =INT(D21/B21) =ROUND(MOD(D20,B20),2) 1.00 =INT(D22/B22) =ROUND(MOD(D21,B21),2) 0.50 =INT(D23/B23) =ROUND(MOD(D22,B22),2) -- Don Guillett Microsoft MVP Excel SalesAid Software "anna" wrote in message ... I work for the company where we still pay the cash wages, before I go to the bank I Have to work out what how many hundreds fifties and so on to get ,I set up on a spreadsheet where I put the amount earn for example $2085 dollars and at this stage I put manually how may hundreds ,fifties ,twenties ,tens and fives goes into that sum is there a code so it can automatically do this for me in excel 2003 Can anyone help Thank you in advance $100 $50 $20 $10 $5 $2,085 20 1 1 1 1 $1,975 19 1 1 1 $2,135 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Need help with coding
Hi Sreedhar
Thank you very much it works Regards Anna "yshridhar" wrote: Hi anna Taking A1 = amount ($2085) b1= 100, c1 = 50, d1 = 20, e1=10, f1 = 5 B2 (100's) = QUOTIENT(A1,100) c2 (50's) =FLOOR(($A$1-$B$2*$B$1)/C1,1) D2 (20's) =FLOOR(($A$1-($B$2*$B$1+$C$2*$C$1))/D1,1) E2 (10's)=FLOOR(($A$1-($B$2*$B$1+$C$2*$C$1+$D$2*$D$1))/E1,1) F2 (5's) =FLOOR(($A$1-($B$2*$B$1+$C$2*$C$1+$D$2*$D$1+$E$2*$E$1))/F1,1) If you want to extend the formula you have to add the other denominations. Best wishes Sreedhar "anna" wrote: I work for the company where we still pay the cash wages, before I go to the bank I Have to work out what how many hundreds fifties and so on to get ,I set up on a spreadsheet where I put the amount earn for example $2085 dollars and at this stage I put manually how may hundreds ,fifties ,twenties ,tens and fives goes into that sum is there a code so it can automatically do this for me in excel 2003 Can anyone help Thank you in advance $100 $50 $20 $10 $5 $2,085 20 1 1 1 1 $1,975 19 1 1 1 $2,135 |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Need help with coding
You are welcome. Glad I could help you.
best wishes Sreedhar "anna" wrote: Hi Sreedhar Thank you very much it works Regards Anna "yshridhar" wrote: Hi anna Taking A1 = amount ($2085) b1= 100, c1 = 50, d1 = 20, e1=10, f1 = 5 B2 (100's) = QUOTIENT(A1,100) c2 (50's) =FLOOR(($A$1-$B$2*$B$1)/C1,1) D2 (20's) =FLOOR(($A$1-($B$2*$B$1+$C$2*$C$1))/D1,1) E2 (10's)=FLOOR(($A$1-($B$2*$B$1+$C$2*$C$1+$D$2*$D$1))/E1,1) F2 (5's) =FLOOR(($A$1-($B$2*$B$1+$C$2*$C$1+$D$2*$D$1+$E$2*$E$1))/F1,1) If you want to extend the formula you have to add the other denominations. Best wishes Sreedhar "anna" wrote: I work for the company where we still pay the cash wages, before I go to the bank I Have to work out what how many hundreds fifties and so on to get ,I set up on a spreadsheet where I put the amount earn for example $2085 dollars and at this stage I put manually how may hundreds ,fifties ,twenties ,tens and fives goes into that sum is there a code so it can automatically do this for me in excel 2003 Can anyone help Thank you in advance $100 $50 $20 $10 $5 $2,085 20 1 1 1 1 $1,975 19 1 1 1 $2,135 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sum by coding | New Users to Excel | |||
How to fix the coding? | Excel Worksheet Functions | |||
How to fix the coding? | Excel Worksheet Functions | |||
coding | Excel Discussion (Misc queries) | |||
Coding | Excel Discussion (Misc queries) |