Rank by time and importance

[greenmike100]

We have an issue dealing with customer enquiries in a timely and priority manner.

We want to be able to assign a level of importance and set a timescale for replying to each enquiry, so we're sort of looking to understand how to rank by time-verses-importance score in excel.

Ideally we would keep a list of outstanding enquiries and run the routine daily, giving us a list based on the score ranking

Any idea where to start ?

rgds

[oldchippy]

Hi,

Welcome to the forum, may be this previous link will help?

http://www.excelforum.com/excel-2007...-in-excel.html

[greenmike100]

yes, thats sort of it....the only difference is that i need to part-rank by the passage of time. So a formula would need to work out the duration from a start date...i.e the longer the enquiry is with us the higher it scores,

Any thoughts

ps....greetings from Stourport !

[shg]

You can add a second key. Try recording a macro, and then we'll help cleaning it up.

[oldchippy]

Hi greenmike from Stourport - just up the road

So a formula would need to work out the duration from a start date...i.e the longer the enquiry is with us the higher it scores

If you have a start date say in A1 then you could use

=A1-TODAY() and format the cell to General

[greenmike100]

aaaggg so frustrating..

This is where i have got to so far (attached)

I suppose i need to understand how to reurn the value for column F if the value in column E is less than the score vaules....any idea how to do that ?

Also, for some reason when i change the Account Importance value in column C it doesnt return the correct score value !...how come ?

Thanks for your help so far....any more would be great.

Mike

[shg]

The formula in F2 should (perhaps) be just =VLOOKUP(E2, $D$14:$E$42, 2)

There must be a value in col D of the lookup table that is less than the smallest value col E, or you'll get an error.

How you combine the values in col D and F to arrive at a final score depends on the relative weight you consider those factors to have.

[greenmike100]

Thanks all !!

Here is the resulting spreadsheet.

So the "importance v timescale" score is now achieved...it's just we assume the timescale for enquiries does not exceed 98 days.

thanks again for your help.