Formula - Multiple "IF's" for end result

[jd706]

Hello,

I would like some help please! I am trying to create a formula to show results of "frequency" based on "accuracy".

My spreadsheet is 150 rows; each row has a frequency result:
[TABLE="width: 189"]
<tbody>[TR]
[TD][/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]Accuracy
[/TD]
[TD]Frequency
[/TD]
[/TR]
[TR]
[TD]≥99.5%
[/TD]
[TD]1x/yr.
[/TD]
[/TR]
[TR]
[TD]≥98.0% but <99.5%
[/TD]
[TD]2x/yr.
[/TD]
[/TR]
[TR]
[TD]<98.0%
[/TD]
[TD]4x/yr.
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]I created this formula, but not so sure it's correct. The formula is carried to each row, but the frequency result in most rows is not correct.
=IF(C5>99.5,"1x",IF(C5>98<99.5,"2x",IF(C5<98,"4x")))
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

Welcome to the Board!

Since nested IF statements stop once a True condition is met, just work your way down the line, like this:
=IF(C5>99.5%,"1x",IF(C5>=98%,"2x","4x"))

Also, I don't know how your numbers are listed, be be aware that 99.5% really equals .995, not 99.5.

 

[Jonmo1]

You can also try a lookup
=LOOKUP(C5,{0,0.98,0.995},{"4x","2x","1x"})

 

[jd706]

Thank you so much! It worked perfectly!

 

[jd706]

I hadn't thought about a VLOOKUP. This worked great as well. Thank you!

 

[Jonmo1]

You're welcome (though look closer, it's not Vlookup)