Multiple IF in single Cell

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

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

Stephen Dunn

Hi Rudy,

this is a little confusing, but I will assume that you mean:

B2<=5 hours, gives a result of 20 + 2.5*B2
5<B2<=7.5 (7:30), gives 25 + 3*B2
7.5<B2<=10, gives 30 + 3*B2

What do you want to happen when B2 is greater than 10?

See if this formula gives the results you are expecting:

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

if you have B2 formatted in hours, you will have to multiply it by 24 to get
the correct figures:

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

However, if neither of these gives the results you are expecting, and no one
else gives a suitable reply, you will have to be a little clearer about your
requirements.

Steve D.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top