Excel, complex IF function & Conditional formating

[cpisthedbb]

Hi,

I've been trying all morning to work out a formula with no luck (will prob take someone on here 5 minutes to solve).

I have the current formula to calculate the following:

=IF(O3<8.8,0,IF(O3<9.89,0.5,IF(O3<11,1,IF(O3<12.16,0.5,0))))

[TABLE="width: 510"]
<tbody>[TR]
[TD]Operator Total Shift Hours Booked
[/TD]
[TD]Less than 80% - 8.8 hrs
[/TD]
[TD]Between 80%-90% (8.8-9.89 hrs)
[/TD]
[TD]Between 90%-100% (9.9-11 hrs)
[/TD]
[TD]Between 100%-110% (11.01-12.16 hrs)
[/TD]
[TD]Over 110% (12.17 hrs)
[/TD]
[/TR]
[TR]
[TD]POINTS[/TD]
[TD]0
[/TD]
[TD]0.5
[/TD]
[TD]1
[/TD]
[TD]0.5
[/TD]
[TD]0
[/TD]
[/TR]
</tbody>[/TABLE]


There may be a shorter way of doing this but my excel skills aren't that advanced.


Within this formula I also want to incorporate the following rules if the operator is a Team leader:

[TABLE="width: 510"]
<tbody>[TR]
[TD]TL Total Shift Hours Booked
[/TD]
[TD]Less than 80% - 9.2 hrs
[/TD]
[TD]Between 80%-90% (9.2-10.34 hrs)
[/TD]
[TD]Between 90%-100% (10.35-11.5 hrs)
[/TD]
[TD]Between 100%-110% (11.51-12.66 hrs)
[/TD]
[TD]Over 110% (12.66 hrs)
[/TD]
[/TR]
[TR]
[TD]POINTS[/TD]
[TD]0
[/TD]
[TD]0.5
[/TD]
[TD]1
[/TD]
[TD]0.5
[/TD]
[TD]0
[/TD]
[/TR]
</tbody>[/TABLE]


Is there a way this can be done in 1 formula or will they have to be two separate columns? I've tried for hours with no luck.



I also want to conditional format the points as well. This might be a lot more complex but is there a way to do conditional formatting which is different if the operator is a team lead?


Any help will be appreciated.

 

[Eric W]

You didn't say how to determine if a person is an operator or team leader. As an example, I assumed that if O2=0, it's an operator, otherwise, team leader:

=LOOKUP(O3,IF(O2=0,{0,8.8,9.9,11.01,12.17},{0,9.2,10.35,11.51,12.66}),{0,0.5,1,0.5,0})

You should be able to see where all the numbers come from.

Conditional formatting should be easy too, just use whatever rule you have to determine if someone is a team leader or not.

 

[cpisthedbb]

Thanks for the reply eric. Apologies should have made it a bit clearer - the O3 refers to the value. Column E3 states wether the person is an operator or a team leader. I’ll try your suggested formula when i get back into the office on thursday morning. Thanks for your help

 

[cpisthedbb]

You didn't say how to determine if a person is an operator or team leader. As an example, I assumed that if O2=0, it's an operator, otherwise, team leader:

=LOOKUP(O3,IF(O2=0,{0,8.8,9.9,11.01,12.17},{0,9.2,10.35,11.51,12.66}),{0,0.5,1,0.5,0})

You should be able to see where all the numbers come from.



Conditional formatting should be easy too, just use whatever rule you have to determine if someone is a team leader or not.

Just had a go at this and it's saying the formula is incorrect. I tried:
=LOOKUP(o3,IF(J3='Team Lead',{0,8.8,9.9,11.01,12.17},{0,9.2,10.35,11.51,12.66}),{0,0.5,1,0.5,0})

I must have entered the formula incorrect somewhere. On my sheet '03' equals the value I want to analyse and 'J3' is the cell which states if the person is an operator or a team lead.

 

[Eric W]

Just had a go at this and it's saying the formula is incorrect. I tried:
=LOOKUP(o3,IF(J3='Team Lead',{0,8.8,9.9,11.01,12.17},{0,9.2,10.35,11.51,12.66}),{0,0.5,1,0.5,0})

I must have entered the formula incorrect somewhere. On my sheet '03' equals the value I want to analyse and 'J3' is the cell which states if the person is an operator or a team lead.

The main problem is that you need double quotes around "Team Lead". It also appears that you have the arrays reversed from what you want. Try:

=LOOKUP(o3,IF(J3<>"Team Lead",{0,8.8,9.9,11.01,12.17},{0,9.2,10.35,11.51,12.66}),{0,0.5,1,0.5,0})

 

[cpisthedbb]

The main problem is that you need double quotes around "Team Lead". It also appears that you have the arrays reversed from what you want. Try:

=LOOKUP(o3,IF(J3<>"Team Lead",{0,8.8,9.9,11.01,12.17},{0,9.2,10.35,11.51,12.66}),{0,0.5,1,0.5,0})

Cheers Eric, all seems to work. I said I was a novice

I'm not brilliant with conditional formatting. How would you go about entering conditional formatting using this formula?

 

[Eric W]

Well, let's say that your points formulas are in column P. Then just select column P, click Conditional Formatting > New Rule > Use a formula > and enter:

=J1="Team Lead"

in the formula box. Click Formatting > and select a fill color. Click OK. Now every cell in column P that has "Team Lead" in the same row in column J will be highlighted.