excel is returning incorrectly

Posted by michael kedor on March 30, 2001 11:05 AM

my percentage column looks like:
0%
50%
75%
90%
100%
110%
120%
130%
140%
150%
170%
200%
240%

my multiplier column is next to the percentage column and looks like:
0
0.5
0.75
0.9
1
1.2
1.4
1.5
1.7
1.8
2.2
2.5
2.6

my equation is:
=VLOOKUP(C2,Percentage,MULTIPLIER)*E13

for some reason when cell C2 returns a percentage of 110% or higher, the multiplier doesn't work correctly. for example, when C2 returns 110% it should multiply E13 by 1.2...instead it is multiplying it by 1.1. any help, and I will be eternally gratefull

mikedor

Posted by Aladin Akyurek on March 30, 2001 1:11 PM

Michael: There is something fishy about using percentages beyond 100% as lookup value. I'm trying to figure out this.

Aladin .5 .75 .9 .2 .4 .5 .7 .8

Posted by Aladin Akyurek on March 30, 2001 1:51 PM

Select both columns of values and name the selected range MULTIPLIERS via the Name Box or via the option Insert|Name Define. (Get rid off the names Percentage and MULTIPLIER via the option Insert|Name|Define.)

Change the formula to

=VLOOKUP(C2,MULTIPLIERS,2)*E13

Aladin

PS. Nothing is wrong with percentage numbers.

Posted by mikedor on March 30, 2001 4:21 PM

THANK YOU!

THANK YOU SO MUCH, ALADIN!