function to split figure into 500k, 200k and 100k

[mango]

any function to do the following? thanks for the help

if my cell A1 is storing 800000, how to make my
cell A3 would be 500000 (limit 500k)
cell A4 would be 200000 (limit 200k)
cell A5 would be 100000 (limit 100k)

if my cell A1 is storing 650000,
cell A3 would be 500000
cell A4 would be 150000

if my cell A1 is storing 450000,
cell A3 would be 450000 only

any function to calculate so that the figure in A1 can split into limit of
500000, 200000 and 100000? i want to automate it and not calculalate manually.

 

[Alan]

any function to do the following? thanks for the help

if my cell A1 is storing 800000, how to make my
cell A3 would be 500000 (limit 500k)
cell A4 would be 200000 (limit 200k)
cell A5 would be 100000 (limit 100k)

if my cell A1 is storing 650000,
cell A3 would be 500000
cell A4 would be 150000

if my cell A1 is storing 450000,
cell A3 would be 450000 only

any function to calculate so that the figure in A1 can split into
limit of 500000, 200000 and 100000? i want to automate it and not
calculalate manually.

Hi,

What if A1 is negative?

What if A1 is 900,000?


Alan.

 

[mango]

dear alan,
no matter what figure in A1. if A1 less than 500k let say 450000 then only
populate cell A3 which is 450000 and A4 & A5 empty.
if cell A1 900k then 500k in cell A3, 200k in cell A4 and 100k in cell A5.
balance in cel A6
thanks. any function to do this? pls help

 

[JE McGimpsey]

One way:

A3: =MIN(500000,A1)
A4: =MIN(200000,A1-A3)
A5: =MIN(100000,A1-SUM(A3:A4))
A6: =A1-SUM(A3:A5)

 

[mango]

thanks alot.

One way:

A3: =MIN(500000,A1)
A4: =MIN(200000,A1-A3)
A5: =MIN(100000,A1-SUM(A3:A4))
A6: =A1-SUM(A3:A5)