| Home |
| Search |
| Today's Posts |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. . |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Assistance for the IF Function | Excel Discussion (Misc queries) | |||
| If Function Assistance | Excel Worksheet Functions | |||
| 1-2-3 @ function to Excel Assistance | Excel Worksheet Functions | |||
| Average function assistance | Excel Discussion (Misc queries) | |||
Threaded Mode
