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

On Sat, 26 Jul 2008 11:01:17 -0700 (PDT), via135
wrote:

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

On Jul 26, 11:01 am, 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:


There may be many solutions. I don't know if this is what your
teacher had in mind, but this is what I came up:

B1: =INT( (A1-1) / 500 )

C1: =INT( ( MOD(A1-1, 500) + 1 ) / 100 )

I suspect the latter can be simplified. I didn't give it more than a
moment's thought.

Let me know what grade I get ;-).


On Jul 26, 11:01*am, via135 wrote:

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

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)

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