Excel 2007 : IF statement with three conditions

**5h1l** · 12-13-2011, 07:12 AM

Hi Guys.

Please can you help me in working out the following:

I currently have the following if statement:

=IF(H3<=5,(D3*H3),((D3*5)+(H3-5)*E3))

Now there are three conditions.

If H3 is less then 1 then show value as 0.

If H3 is between 1 & 5 then do ((H3<=5,(D3*H3) in formula)

Otherwise H3 must be 5 or over and therefore do ( (D3*5)+(H3-5)*E3) in formula)

Cheers.

Any ideas. I only have to deal with one if statement and never with three conditions.

**Fotis1991** · 12-13-2011, 07:31 AM

Hi

If i undertoond well, then try this formula:

=IF(H3<0;0;IF(OR(H3>1;H3<5);(D3*H3);IF(H3>=5;(D3*5)+(H3-5)*E3)))

Hope to helps you.

**5h1l** · 12-13-2011, 07:42 AM

Hi Fotis. Thanks for the quick response. When i tried your formula it came up as an error and had The first part (...<0;0;...) highlighted.

should it not be "<1;0..."? As we are trying to say if the value is less then 1 then do nothing. Does your formula read, if its less then 0? There wont be any values returned which will be less then 0. smallest will be 0.1.

I just tried replacing the 0 with 1 and still had the same error.

**Fotis1991** · 12-13-2011, 07:54 AM

Hi again

i attach you a sampleworkbook.

Take a look there and let me know, if something is wrong...

**5h1l** · 12-13-2011, 08:38 AM

Hi Fortis, thanks for your example. and i really appreciate your support on this.

When h3 has a value of less then 1 (e.g 0.1 or 0.9) the value in the results box does not = 0. The first condition should be if the value is less then 1, then return a 0 in the results box.

Regards

Shil

**Fotis1991** · 12-13-2011, 08:47 AM

Hi Shil

Then you need this formula:

=IF(H3<1;0;IF(OR(H3>1;H3<5);(D3*H3);IF(H3>=5;(D3*5)+(H3-5)*E3)))

Hope to helps now..

**dip11** · 12-13-2011, 08:50 AM

If(h3<1,0,if(h3<=5,(d3*h3),((d3*5)+(h3-5)*e3))

**5h1l** · 12-13-2011, 10:36 AM

Originally Posted by dip11

If(h3<1,0,if(h3<=5,(d3*h3),((d3*5)+(h3-5)*e3))

Thanks dip. Yours worked a treat.

Thanks Fotis for your help anyway.

Have a great christmas all.

regards

Shil

**dip11** · 12-13-2011, 10:52 AM

Glad it worked. One thing to consider though is that it won't work properly for empty cells or cells with text. Those will return 0 using the formula.
Let me know if you need those to show as blank instead.

Otherwise, just mark the thread as solved (advanced edit first post, select prefix)