Multiple IF in single cell

[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

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...

 

[Kolja]

=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").

 

[macropod]

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

 

[rudy3107]

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),"")))))