# Need assistance with how or even if this is possible (function)

M

#### Marty

I want to be able to put a value in a cell A1 (aka # balls sold) and have A2,
aka (ball sales rating) convert it based on the following criteria to a
preset "rating". EX: I sell red balls. I have goals set in place based on
the number of balls I sell and the ratings are based on a 1 through 5 scale
(i.e 1-50 balls is a 1 rating, 60-100 balls is a 2 rating and so on). The
actual scales are difficult to remember and there are several of them hence
the need to simplify the way I am trying to do.

So, what I would like to do is to be able to key in a1 that I sold, say 10
balls and have a2 take this number, match it against the pre-filled scales
and place the correct sales rating in cell a2. I'm thinking this might fall
under an if - then (if a1 is 1-50, a2 will return the result 1) function but
can't seem to figure it out. Am I on the right track at all? Is this even
possible? Thanks in advance if anyone can provide some assistance and get me
going in the right direction.

P

#### Pete_UK

What would you like to see returned if you sold between 51 and 59
balls in your example?

I assume that your rating scales are continuous. You need to set up a
little table somewhere (eg L1:M5) like this:

1 A
51 B
121 C
181 D
251 E

where you only need to list the starting point for each range (i.e.
this assumes that the first range covers 1 to 50, the second range is
51 to 120 etc). Just put the correct numbers in for your situation.

Then you can have this formula in A2:

=IF(OR(A2="",A2<L1),"",VLOOKUP(A2,L\$1:M\$5,2))

and the appropriate letter will be returned (or you could make it a
number in the table, if prefered).

Hope this helps.

Pete

P

#### Per Jessen

HI

This should get you started (in A2):

=If(A1<1,"",IF(A1<=50,1,IF(A1<=100,2,IF(A1<=150,3,4))))

Regards,
Per

L

#### Luke M

It's quite possible. You will need to setup a lookup table correlating your
sales to a rating. For now, let's assume every 50 balls increases the rating
(up to 5).
In B1:C5

1.........1
51.......2
101.....3
151.....4
201.....5

Your formula then becomes:
=LOOKUP(A1,B1:C5)

Note that each value in the B column is the lower boundary of the section
that you want associated with that rating. Column B must be sorted in
ascending order.

P

#### Pete_UK

Sorry, I just realised the formula should be looking at cell A1:

=IF(OR(A1="",A1<L1),"",VLOOKUP(A1,L\$1:M\$5,2))

Hope this helps.

Pete

M

#### Marty

Alrighty. Been busy, haven't had an opportunity to come back and give a
status update. The original reply got me on my way. Thanks to all who
replied. I was able to figure it out and get what I want accept for one
issue. I want this to be able to be copied on down the sheet indefinately.
The sheet is used for daily tracking of teammates sales and their ranking on
the 1-5 scale. I am able to get the function to work as follows.

J K L M N O

1 Notes to user entered on this line...........

2 Sales Sales Rating Lookup Fields
3 \$0.73 5 Sales
4 \$0.38 5 \$0.00 1
5 \$0.24 5 \$0.11 2
6 \$0.17 2 \$0.25 3
7 \$0.14 2 \$1.00 4
8 \$0.11 2 \$2.00 5

Here is the formula as entered, the lookup fields reside at N4-N8 and O4-O8.
In K3 the formula is " =LOOKUP(j3,N4:N8,O4:O8) "

Works great, but when I copy this down the page, it's wanting to move the
lookup fields up by one for each new line. ex: pasting the formula into K4,
the result is
" =LOOKUP(j3,N5:N9,O5:O9) " then I have to go in and change the lookup cell
references back to n4:n9, o49. For an indefinate sheet, or even one that
runs for just a few months, this is alot of tedious work and I know there has
to be a way to lock that portion of the formula to those particular lookup
cells. Thanks again to all posters from before and any future ones that
provide further assistance.

G

#### Glenn

Look at "Switch between relative, absolute, and mixed references" in the help file.

G

#### Gord Dibben

=LOOKUP(j3,\$N\$4:\$N8,\$O\$4:\$O8)

Gord Dibben MS Excel MVP

M

#### Marty

Got it, works like a charm. Thanks much!

Glenn said:
Look at "Switch between relative, absolute, and mixed references" in the help file.

.