On Jul 26, 11:44*pm, Harlan Grove wrote:
via135 wrote...
i'm having values in multiples of 100 in COL A.
i want the quotients in COL B [divisor 500] * & COL C [divisor 100]
explained as under:

COL "A" * COL "B" * * * COL "C"

100 * * * * *0 * * * * * * *1
200 * * * * *0 * * * * * * *2
500 * * * * *0 * * * * * * *5
600 * * * * *1 * * * * * * *1
700 * * * * *1 * * * * * * *2
1000 * * * * 1 * * * * * * *5
1200 * * * * 2 * * * * * * *2
1500 * * * * 2 * * * * * * *5
1600 * * * * 3 * * * * * * *1
1900 * * * * 3 * * * * * * *4
2000 * * * * 3 * * * * * * *5
2100 * * * * 4 * * * * * * *1
2500 * * * * 4 * * * * * * *5
50000 * * * 99 * * * * * * *5

...

Any particular reason you want, e.g., 2000 as 3x500 + 5x100 rather
than 4x500?

Anyway, try these formulas. With amount in A3,

C3: *=INT(MOD(A3-100,500)/100)+1
B3: *=INT((A3-C3*100)/500)

thks ..lars, joeu & harlan..!!!
all the three exactly gives the results what i want..!

****Any particular reason you want, e.g., 2000 as 3x500 + 5x100 rather
than 4x500? ****


just to check up disbursement of notes in ATM..that's all..!

thanks all.!!

-via135