If statement help

[merlot]

I have a problem building a formula to do the following; I have a give
number which I want to split up to allocated levels.
example;
240000
Cell1 allocated the first 4000
Cell 2 the next 16000
Cell 3 the next 50000
Cell 4 the balance

However, if the number is only 2000 I wish that cells 2, 3 & 4 record
zero.

I have been using IF statements however I am not sure this is correct

 

[Harald Staff]

Hi

There are several ways to do this, so there is no single correct way. IFs
should be perfectly ok. I'd do this:

B1:
=MIN(A1,4000)*(A1>0)
C1:
=MIN(A1-4000,16000)*(A1>4000)
D1:
=MIN(A1-20000,50000)*(A1>20000)

I'm not sure what you mean by Balance.

HTH. best wishes Harald

 

[merlot]

Thank you Harald

Your assistange has been most valuable.