Rank by time and importance

**oldchippy** · 06-23-2010, 05:06 PM

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** · 06-24-2010, 08:36 AM

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** · 06-24-2010, 12:36 PM

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** · 06-25-2010, 07:46 AM

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.