Multiple IF in single cell

  • Thread starter Thread starter rudy3107
  • Start date Start date
R

rudy3107

Hi, i`m newbi in Xcell.
I want to calculate simple overtime

If 5 Hours a day $20+ 2.50*B2
If between 5 to 7.30Hours then $25+3.00*B2
If more 7.30Hours and Less than 10Hours then $30+3*B2


<=5 = 20+2.50*B2
=5:<=7.30 = 25+3*B2
=7.30<=10 = 30+3.B2
Click to expand...

i wrote like this
=IF(B2<5,SUM(20+2.50*B2),"",IF(B2>5,SUM(25+3*B2),"",IF(B2<7.30,SUM(25+3*B2),"",IF(B2<7.30,SUM(30+3*B2),"",IF(B2>10,SUM(30+3*B2),""))))


There is Error

Any Xperts...
 
=IF(B2<5;SUM(20;(2,5*B2));(IF(B2<7,3;SUM(25;3*B2);IF(B2<10;SUM(30;3*B2);""))))

I tested it with all examples and it worked!
I do not know if there's difference 'cause I'm using Serbian language. If it won't work, try to change all "," characters with "." ("2,5" will be "2.5").
 
Hi Rudy,

The are some issues with the example you gave:

What if B2<0 or B2=0 or B2=10 or B2>10?

As described, if B2=-5 then the result is 7.5, if B2=0 then the result is
20, if B2=0 then the result is 57, and B2=>10 then the result is 0 ...

Anyway, a formula that gives effect to the example you described, using
decimal hours (i.e. 7.5 instead of 7.30) is:

=(B2<10)*(20+(B2>5)*5+(B2>7.5)*5)+(B2<10)*B2*(2.5+(B2>5)*0.5)

Cheers
 
Thank you buddy
i changed like this

=IF(B2<=5,SUM(20,(2.5*B2)),(IF(B2>5,SUM(25,(3*B2)),(IF(B2<7.3,SUM(25,3*B2),IF(B2<=10,SUM(30,3*B2),"")))))
 
Back
Top