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

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.

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

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



On Feb 11, 7:01*pm, Marty wrote:
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.

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

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

"Marty" skrev i meddelelsen
...
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.

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

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Marty" wrote:

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.

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

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

On Feb 11, 7:23*pm, Pete_UK wrote:
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

On Feb 11, 7:01*pm, Marty wrote:



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.- Hide quoted text -

- Show quoted text -

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

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, o4:o9. 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.


"Luke M" wrote:

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Marty" wrote:

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.

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

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


Marty wrote:
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, o4:o9. 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.


"Luke M" wrote:

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Marty" wrote:

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.

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

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


Gord Dibben MS Excel MVP

On Tue, 16 Feb 2010 06:58:04 -0800, Marty
wrote:

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, o4:o9. 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.


"Luke M" wrote:

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Marty" wrote:

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.

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

Got it, works like a charm. Thanks much!

"Glenn" wrote:

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


Marty wrote:
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, o4:o9. 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.


"Luke M" wrote:

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Marty" wrote:

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