Formatting Percentages/LOOKUP using a Percentage

[eed]

I have a column in a spreadsheet in which I need to return a letter
sequence/"score".

The sequence is based on two criteria:
1. LOOKUP - for which the reference data is a list of percentages in the
following format: 1.00%.
2. A column containing a formula which calculates a percentage.

The letter sequence/score will not generate, and shows as #N/A.
I have tried copy/paste special values to remove the formula, but the
sequence will still not generate because the percentages are in the following
format: 1.000000000000000%.

I have to manually re-key the percentage for every cell in order to get the
score to generate in the 'score' column.

Please help - thanks!

 

[Dan DeHaven]

I'm not sure how you have your formula structured or how the percentag
column is formated, either could potentially be causing your problem
But if say your percentage column was "F" this formula should wor
*=LOOKUP(F2,{0,0.6,0.63,0.67,0.7,0.73,0.77,0.8,0.83,0.87,0.9,0.93,0.97},{"F","D-","D","D+","C-","C","C+","B-","B","B+","A-","A","A+"})*
You need to remember to put the decimals in your "lookup_vector
arguments and remember the "{}" in your formula.

If this doesn't help or shed light on the problem please reply with
sample of the LOOKUP formula as well as a sample of the formula that i
calculating the percentage.

Best of Luck,

Dan

 

[eed]

I tried what you sent, but still got #N/A, so here are the formulas, etc:

1. The 'variance' column (O) contains this formula: TY/LY-1, and shows a
percent to 2 decimal places.

2. The 'score' column (Q), contains this formula:
=VLOOKUP(P5,scoring!$A:$B,2,0) and is referencing another wksheet, 'scoring'

3. The scoring tab contains a column (A) with a range of possible
percentages, as well as a column containing the corresponding letter score.
(see below for example)
-100.00% DNM
-99.99% DNM
-99.98% DNM
-99.97% DNM
-99.96% DNM
ETC...

Thanks!!

 

[Dan DeHaven]

Sorry, I didn't realize you meant a VLOOKUP. No biggie.

I built a sheet based on what you described and I think I've found th
issue. Either in the Variance column "O" or in Score lookup column "A
aren't completely precise to the 2nd decimal place. Many if not most o
the numbers have a number other than "0" way out to the 13th decima
like "-99.440000000000100%". And as you have your VLOOKUP's 4th argumen
(range_lookup) set to "0" (*same as FALSE), meaning it requires an exac
match and thus causing the #N/A error. No matter how you adjust th
decimal place setting on any number format, the details in unsee
decimal places still exist. Same is true if you use a "ROUND" functio
or "FLOOR" / "CELING" function, they merely mask the true value which i
what would cause the vlookup to fail.

There is a simple solution depending on how exact your match needs t
be. You could change the last argument to "1" or "TRUE" as your 4t
argument and the formula will match the closest value. The formula woul
look like "=VLOOKUP(O5,scoring!A:B,2,1)" o
"=VLOOKUP(O5,scoring!A:B,2,FALSE)" works the same.

Or, if you need to be as precise as possible here is a solution.

1st. On the scoring tab input this formula somewhere beyond your use
range, say in cell "F1" (if "F" is empty) and input this formul
"=TEXT(A1,"#.00%")" and drag it down so that it calculates for all you
lookup scores in column "A" and then copy the entire range of formula
in column "F" and paste them as values over the number in column "A"
Then you can delete the formulas in column "F".

2nd. On the tab with the variance calculation replace your formula wit
this formula "=VLOOKUP((TEXT(O1,"#.00%"),scoring!$A:$B,2,0).

When your dealing with numbers to the precision it probably best t
transform/convert them to text (Via the TEXT Function) so that th
lookup will work correctly. As the TEXT function formats the value t
just two decimal places you can avoid the #N/A issue.


Give it a try and let me know how it goes.

Da

 

[eed]

grrr. have tried so many times and it just won't work...the formula for the
'scoring' tab worked fine, but it's the formula for the score column
vlookup/conversion to text that isn't working for me. I am getting the
message that I have an error in my formula every time. This is great though -
totally on the right track, just can't for the life of me figure out what the
error is!

 

[Dan DeHaven]

If you don't mind, attach the spreadsheet or at least a subset of th
workbook that has the needed parts and I can debug the problem for you.

I know it's frustrating but usually there is some small thing that i
overlooked and another set of eye's can usually help.

I'm sure I could get it fixed within 12 hours at the most if you ca
attach it.

Hang in there....

Dan