Need a formula...

[TLR75]

I have been given a task to collect info and create several spreadsheets
but I have one more formula to create. Here is it...

The formula has to callers receiving points for the amount of Western
Union sales they have for the month. Let say the entire department
AVERAGE (this number will change every month) was 161 sales; then 161
would be the median.

If the caller had 171 - 180 sales then he or she gets +1 point.
If the caller had 181 - 190 sales the caller gets 2 points, etc..
until the mark of 321-330 in which they get 16 points.

So, if I enter the number 255 in the cell then the answer would be 9.


The same goes for a negative number. (no enough selling)

If the caller had 142 - 151 sales then he or she gets -1 point.
If the caller had 132 - 141 sales the caller gets 2 points, etc...
until the mark of 82 - 91 in which they get -7 points.

So, if also enter the number 111 in the cell then the answer would be
-5.

I can clarify things if anyone here needs more explanations.


I have attached the EXCEL file. The cell number is on Column P.
The answere Im looking for for is on Column Q

File Attached: http://www.exceltip.com/forum/attachment.php?postid=274158 (victor4.xls)

 

[John Wilson]

TLR75,

Sounds like a good time to use a VLookup table.

Try this in a new workbook and modify to suit.

In cells E1:F6 place the following table:
E F
0 -16
10 -15
20 -14
30 -13
40 -12
50 -11

An any cell, place the following formula:
=VLOOKUP(B1,E1:F6,2)

now try placing values in B1

Notes:
The table "has" to be in order.
The lookup will find the last value that's not over the value
you're looking up. e.g. If you look up "33", you'll get the
corresponding value found for "30"
Extend the table as much as you need to.
Substitute your values in the table.
Modify the formula to include the extended range.

John