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

[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.

 

[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

 

[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

 

[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.

 

[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

 

[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.

 

[Glenn]

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

 

[Gord Dibben]

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


Gord Dibben MS Excel MVP

 

[Marty]

Got it, works like a charm. Thanks much!

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


.