How to split a value based on diff criteria?

[sinfulloyolite]

Hello,

I need a formula which could split a value into 4 based on following criteria:

If I enter a value as 600 in A2:

I want tht to be split into 0-150 (B2); 151-300 (C2); 301-500 (D2); 501 & above in (C2)

It nothing but the cells show display as A2 = 600 ; B2 = 150; C2 = 150; D2=200 & D2 = 100 ...

Total of B2, C2, D2 = 600.

Could anyone Sort this out?

Thanks,
Prem

 

[pgc01]

Hi Prem

Try:

B2: =MIN(A2,150)
C2: =MIN(MAX(A2-150,0),150)
D2: =MIN(MAX(A2-300,0),200)
E2: =MAX(A2-500,0)