don't know what I am doing!

excelnewbie

New Member
Joined
Aug 28, 2002
Messages
2
Hi-
I have been put to the task of preparing a spreadsheet. Scary enough, but they require formulas! yikes!
Here is my plight:

I have column A, which contains employee names. I have column B, which contains the Days Outstanding. I then have column C, which will contain the rank... the ranking is 0-59 days = 10, 60-64 days = 9, 65-69 days = 8, and so on, all the way down to 110+ days = 0
what I need is once I enter in the info to columns A and B, I need the ranking to automatically pick up in column C. Is this possible????

Thank you so much for any help you can provide to a technically challenged individual!

-Gaia Marie
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
you need the rank() function! check it out in the help file...

For numbers in col B from b1:b50 :

=rank(b1,$b$1:$b$50,1)

(where the 1 at the end is for an ascending order)

Paddy
 
Upvote 0
I don't think that was what he/she was asking.

{=MIN(IF(B1>{0,60,65},{10,9,8},9999))}

Try that formula.
This message was edited by zacemmel on 2002-08-29 14:22
 
Upvote 0
If I understand you correctly you want column C to look up a number from the ranking table you mentioned in your post. You can do this by inserting your table into your spreadsheet, and by then using the vlookup function. First of all, insert the following table into cells H1:I11 :

0 10
60 9
65 8
70 7
75 6
80 5
85 4
90 3
95 2
100 1
105 0

Then, place the following formula into cell C1:

=VLOOKUP(B1,$H$1:$I$11,2)

This formula will take the no. of days outstanding you have entered into cell B1, and lookup the proper rank from the table in cells H1:I11, and return the rank no. to the cell C1. The next step would be to copy the formula down column C to the last row containing an employee name. If you need further help with the vlookup function, consult excel's help for this function. hope this helps you,

kevin
 
Upvote 0
zacemmel's solution is much more simple, nice thinking
 
Upvote 0
hello -
apologies if my note was confusing. Italian is my language, though I have been speaking English for a few years. However, it gets a little confused when I get nervous, and boy was I nervous! I needed to get this to my boss tonight! Thank you zacemmel - your formula worked great (right after I got a co-worker to help me figure out how to get the formula to work with those { } at either end!)

thank you all for your help!

buona sera,
Gaia Marie
 
Upvote 0

Forum statistics

Threads
1,217,316
Messages
6,135,816
Members
449,965
Latest member
Ckl43

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top