finding out denominations.!

[via135]

hi

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

 

[Lars-Åke Aspelin]

hi

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
.
.
.
so on


any hlp pl..?


-via135

Try the following formulas for cell B1 and C1 respectivele:

=INT((A1-1)/500)

=CHOOSE(1+MOD(A1,500)/100,5,1,2,3,4)

Copy them down as far as needed

Hope this helps / Lars-Åke

 

[Harlan Grove]

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)

 

[via135]

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