Formula to calculate result based on range.

010wolf · Dec 13th 2012

I'm new to using the "if" function so I apologize for the amateur question however I've been racking my brain on this one.

I have a scorecard that looks something like this:

90% or greater=3
80%-89%=2
70%-79%=1
0%-69%=0

I need the "cell" to react accordingly and I'm lost. Any help would be greatly appreciated. Thanks.

Windy58 · Dec 13th 2012

Re: Formula to calculate result based on range.

010wolf

The best way would be to use the VLOOKUP function.

Create a table of the percentages and thier values.
Column A Column B
0% 0
70% 1
80% 2
90% 3

The vlookup would be =VLOOKUP(D1,$A$1:$B$4,2) where D1 is the percentage you are looking for.

HTH

Windy

AAE · Dec 16th 2012

Re: Formula to calculate result based on range.

You could use a lookup formula:

=LOOKUP(A1,{0,0.7,0.8,0.9},{0,1,2,3})

Participate now!