IF I could get help with my IF line please 3

[LanceBergeron]

I created a timesheet whereby the tech puts his hours in a cell and the formulas figure out overtime and bank time. For instance

=0+IF(B12=8,B12-8,IF(B12=8.5,0.5,IF(B12=9,1,IF(B12=9.5,1.5,IF(B12=10,2,IF(B12=10.5,2.5,IF(B12>=11,3,)))))))

The problem is, I am only allowed 7 IF's in a formula and I want to add in a different range. The above covers .5 hour intervals up to a maximum of 3 hours of overtime. Is there an easier way to say:

If "B12 is greater than 8 but less than or equal to 12, muliply by 1.5. If greater than 12, 4

Thanks in advance!

 

[TonyJollans]

Hi LanceBergeron,

Not quite sure I can square your existing formula with your request but this should give you the general idea ..

=IF(AND(B12>8,B12<=12),B12*1.5,4)

Enjoy,
Tony

 

[SkipVought]

Hi,

Does this do it?

=(B12-8)*if(B12<8,0,if(b12<=12,1.5,4))

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Chattin]

Just an aside but have a look at

http://j-walk.com/ss/excel/usertips/tip080.htm

to get round the nested if's limit

HTH

 

[LanceBergeron]

=0+IF(AND(B11>8,B11<=12),B11-8,IF(B11<=8,0,IF(B11>12,4,)))

Thanks for all your help, this is the resulting working formula!

Lance