if/then statement to add 1, 2 or 3 based on conditional format

[squeakums]

I am working on this formula and I need assistance. An if/then statement to add 1, 2 or 3 based on conditional formatting - for example if the format is Red then put a 3.

Or, perhaps set it up for a range if possible:

=IF(OR(D10="-"),"",IF(OR(D10=92%),1))

Instead of just 92% can I make it a range 90% - 92.% then 2 points.

Although, it would be a lot easier to set it up if D10 is equal to Orange then put point 1.

Please help

 

[texasalynn]

so why are you using OR when there is only one condition?
=IF(OR(D10="-"),"",IF(OR(D10=92%),1))

 

[squeakums]

Good question, apparently I am stuck on how to write this formula. Any suggestions?

 

[jgol]

Best bet would be making some sort of function to do this.

colorcode(grade,other input)

If grade >= 0.9 Then

colorcode = 1

ElseIf grade < 0.9 And <= 0.8 Then

colorcode = 2

ElseIf grade < 0.8 And <= 0.7 Then

colorcode = 3

End If

End Function

Hopefully this will put you on the right track..

 

[Scott Huish]

Why a UDF, why not just use LOOKUP?

=IF(ISNUMBER(D10),LOOKUP(D10,{0,3;0.8,2;0.9,1}),"")

 

[squeakums]

That might work - what is the

=IF(ISNUMBER(D10),LOOKUP(D10,{0,3;0.8,2;0.9,1}),"")

.80 .90..

The issue is the range is like 93.5 to 95.5 for yellow = 1 point

96 - 105 for green - no points (don't need to write anything for this)

if 110 or more - 3 points, or if less than 89% 3 points

If 90 - 93% - 2 points

What is the best way to write this? I would prefer to stick with a non-macro if possible.

 

[squeakums]

Is there a way that I can setup like this?

=IF(D22=F126:F1125,1), IF (D22=A126:A375, 2), IF (D22=B126:B375, "")

So that it looks at each number in each range and if it equals to any of those then creates a 1, 2, etc.