#1
|
|||
|
|||
Formula to return a value of 1,2,3 or based on a set criteria
Dear Experts
Help! I need to introduce a set of criteria in my spread sheet (Office 2010) based on a date. I have a date in cell L5 and want to return a value of 1,2,3 or 4 in M5 based on the following criteria: <4 months to return a value of 1 =>4 – <6 months to return a value of 2 =>6 – <12 months to return a value of 3 =>12 months to return a value of 4 Any help would be greatly appreciated Gordon |
#2
|
||||
|
||||
Does this work?
=LOOKUP(DATEDIF(TODAY(),L5,"m"),{0,4,6,12},{1,2,3, 4}) |
#3
|
|||
|
|||
WOW! Thank you so much.
I will give it a go tomorrow. Gordon |
#4
|
||||
|
||||
No Problem.
If the dates in L5 are before today, then switch the L5 and TODAY() parameters... |
#5
|
|||
|
|||
The dates in L5 will definitely be before today, and can be anything between 3 and >12 months.
Just so I don't mess it up how would it look if I changed the parameters? Gordon |
#6
|
||||
|
||||
Like this:
=LOOKUP(DATEDIF(L5,TODAY(),"m"),{0,4,6,12},{1,2,3, 4}) |
#7
|
|||
|
|||
Excellent, thank you.
|
#8
|
|||
|
|||
I tried this today and hey presto. Thank you very much.
Appreciated! Gordon |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Return Max Text or Number Value based on Criteria | kwilson307 | Excel | 1 | 04-20-2017 05:07 AM |
Sum based on multiple criteria (one of the criteria is not unique) until a threshold is reached. | phillipsdp | Excel | 1 | 11-09-2016 12:53 AM |
Select value from table based on two criteria | gebobs | Excel | 4 | 07-21-2016 08:26 AM |
How to return a value in a data table using vertical and horizontal criteria | oswald | Excel | 1 | 02-08-2015 02:01 AM |
Change values in cells based on criteria | SaneMan | Excel Programming | 2 | 02-02-2012 07:58 AM |