An If Function That's Too Complicated For Me

[oliverhj]

Hi everyone,

My Excel knowledge is at a fairly basic level, so I'm sure this is very straightforward for an Advanced User.

I've been playing around with the IF Function for ages and can't quite get it to go as there are multiple logical tests to include in one cell and that's a bit much for me.

Attached is the sheet I'm working on.

I'm looking for the If function in J3-6, M3-6, P3-6 and S36. If the current figure is between:

0-49.9% I want the Factor to be 0.
50-74.9% I want the Factor to be 2.
75-99.9% I want the Factor to be 4
100-124.9% I want the Factor to be 6
125-149.9% I want the Factor to be 8.5
150+ I want the Factor to be 10

If you could help me with this "advanced" (to me anyway) If function that would be great.

Thank you allHow To If.xlsx

[bebo021999]

First, get % of Quarter 1: current/Quota: I3/INDEX($B$3:$E$3,MATCH(I2,$B$2:$E$2,0))

Below formulas are getting Factor in J3 then copy to M,P,S column:

With IF:

Please Login or Register  to view this content.

Or with LOOKUP much shorter:

Please Login or Register  to view this content.

[mcmahobt]

I'm looking for the If function in J3-6, M3-6, P3-6 and S36.[/ATTACH]

If you are looking for the appropriate factor, shouldn't you be looking to populate the K3 cell? If that's the case, you could use this:

Please Login or Register  to view this content.

But this is not dynamic, since there are a few formatting problems that would stand in the way of creating a simple lookup formula (merged cells, factor values in between percentage values).

If I assumed incorrectly, can you please show what you expect your results to look like?

[mcmahobt]

I'm looking for the If function in J3-6, M3-6, P3-6 and S36.[/ATTACH]

If you are looking for the appropriate factor, shouldn't you be looking to populate the K3 cell? If that's the case, you could use this:

Please Login or Register  to view this content.

But this is not dynamic, since there are a few formatting problems that would stand in the way of creating a simple lookup formula (merged cells, factor values in between percentage values).

If I assumed incorrectly, can you please show what you expect your results to look like?