Formula which awards different value according to percentage range

**robwein83** · 11-05-2011, 06:26 PM

I'm needing help on formulas to propagate numbers into the empty column highlighted in the picture.

http://imageshack.us/f/842/unled2dlz.jpg/

I need to award "points" depending on the % range from the columns on the left.

0-35% = 2 points
36-50% = 4 points
51-65% = 8 points
66% + = 10 points

What is the formula to do this?

**jeffreybrown** · 11-05-2011, 06:34 PM

Hello & Welcome to the Board,

How about...

=LOOKUP(A1,{0,0.36,0.51,0.66},{2,4,8,10})

**robwein83** · 11-05-2011, 08:41 PM

Originally Posted by jeffreybrown

Hello & Welcome to the Board,

How about...

=LOOKUP(A1,{0,0.36,0.51,0.66},{2,4,8,10})

Thank YOu for the reply.

It's only giving me values of "2" back.

**jeffreybrown** · 11-05-2011, 08:45 PM

Try this sample...

**robwein83** · 11-08-2011, 09:55 PM

Thanks....

Still having difficulty.

Will this still work if the cell that I'm pointing to "LOOKUP" has a formula to generate the percentage it displays. In other words, will "LOOKUP" look at what is visually displayed in the cell as the result of a formula. Or will it try to look at the formula for that cell?

The cells that I am using to "LOOKUP" are percentages generated from SUMS of other cells. Does this conflict?

Will I instead have to LOOKUP the sum and apply points to the results of the original formula which gives me what is displayed as percentages?

I'm guessing that I will need a formula which instead looks at the originating sells and IF the SUM results in a number between - for example - 0-25 or 26-50 or 51-75 or 76-100 then display 2 or 4 or 8 or 10?

Also, is there a way to use the LOOKUP to apply points to percentages with tenths and/or hundredths included in the percentage?

Sorry, I am a complete novice to Excel formulas and don't know any of the logical rules which apply.

**robwein83** · 11-08-2011, 10:05 PM

Salon Scorecard.xls

I've attached the excel file I'm working on to make more sense and you can see what's going on in my cells.

**jeffreybrown** · 11-08-2011, 11:55 PM

Will this still work if the cell that I'm pointing to "LOOKUP" has a formula to generate the percentage it displays. In other words, will "LOOKUP" look at what is visually displayed in the cell as the result of a formula. Or will it try to look at the formula for that cell?

Yes, the lookup see's the result in the cell even if it is derived via a formula...

The cells that I am using to "LOOKUP" are percentages generated from SUMS of other cells. Does this conflict?

No...

Will I instead have to LOOKUP the sum and apply points to the results of the original formula which gives me what is displayed as percentages?

Don't see why, everything is working fine as far as I can see...

I'm guessing that I will need a formula which instead looks at the originating sells and IF the SUM results in a number between - for example - 0-25 or 26-50 or 51-75 or 76-100 then display 2 or 4 or 8 or 10?

I've looked at the attached spreadsheet and I don't see any problem. What exactly do you see not diplaying or returning the value you expect?

Also, is there a way to use the LOOKUP to apply points to percentages with tenths and/or hundredths included in the percentage?

Not sure I understand your question here. Do you have an example which could clear it up for me?

**robwein83** · 11-09-2011, 03:37 PM

Thank you again for your thorough help.

Here's an example of an error.

For example look at the "Gift Card" cell: =LOOKUP(D45,{0,0.2,0.3,0.5},{2,4,8,10})

If I manually change cell D45 to test this, I'm seeing that the point values will change when my percentage changes between 20%, 30%, 50%, and isn't corresponding with single digit percentages (which I want it to) at 2%, 3%, and 5%.

Also, I need to code some cells backwards, so that the lower the percentage the higher the point value.

Attached is a PDF of a table which I am trying to code my cells for. The table is at the top of page 2, and shows the points to award depending on the percentage. The table also has percentages with tenths of a percent. That was my other question. How to designate points to percentages which include tenths.

Salon Scorecard.pdf

At this point I know that I am probably asking for more than most. Everything is very appreciated.

**jeffreybrown** · 11-10-2011, 12:12 PM

Just change the percentage inside the Lookup function...

Please Login or Register  to view this content.

Also, I need to code some cells backwards, so that the lower the percentage the higher the point value.

I'm not entirely sure what you mean, but maybe an If statement to switch between different formula's.

Example:

Please Login or Register  to view this content.

This formula will look at D45 and if equal to or less then 50%, then the first formula will be applied, or if not, then the second. Just remember, with the Lookup function, the values in lookup_vector must be placed in ascending order.

Look in Excel help for more info on the Lookup function.