Sum if greater than but less than

[turnej]

Hi Guys,

I need to calculate the values of a particular cell if certain conditions are met - I will try and explain

If C11=12000 then the sum will be 30% of the value between 5000 and l0000 and 50% of the value above 10000

eg.

0-5000=0
5000-10000*30%=1500
2000*50%=1000

Formula result 3000

 

[oldbrewer]

Hi Guys,

I need to calculate the values of a particular cell if certain conditions are met - I will try and explain

If C11=12000 then the sum will be 30% of the value between 5000 and l0000 and 50% of the value above 10000

eg.

0-5000=0

5000-10000*30%=1500

2000*50%=1000

Formula result 3000

12000

2500

formula is

=IF(D18<5000,0,IF(D18<10000,(D18-5000)*0.3,5000*0.3+(D18-10000)*0.5))

<colgroup><col span="13"></colgroup><tbody>
</tbody>

 

[turnej]

wow thank you much appreciated

 

[oldbrewer]

happy to help - it stops me watching daytime TV.....

 

[West Man]

Another way, slightly shorter without IF: =(D11 >=5000)*(((D11-5000)*0.3)+MAX(0,(D11-10000)*0.2))

 

[oldbrewer]

=(D11 >=5000)*

so what if D11 = 4999

 

[West Man]

=(D11 >=5000)*

so what if D11 = 4999​

Then that portion should evaluate to false, be coerced to zero and when multiplied by the rest of the formula would result in zero

 

[oldbrewer]

thanks - I learned something else - great !