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

[Alan]

"mango" <ma...@discussions.microsoft.com> wrote in message
news:56588D24-347E-4362...@microsoft.com...
>
> 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)

In article <7BDB08AF-3B5A-4859...@microsoft.com>,

[mango]

thanks alot.