Sum if greater than but less than

turnej

New Member
Joined
May 4, 2013
Messages
23
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
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
11,003
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
120002500
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>
 

West Man

Well-known Member
Joined
Mar 27, 2006
Messages
1,175

ADVERTISEMENT

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

West Man

Well-known Member
Joined
Mar 27, 2006
Messages
1,175
=(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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,462
Messages
5,528,942
Members
409,848
Latest member
Blomsten
Top