Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-01-2017, 12:00 PM
Highlander01 Highlander01 is offline Formula to return a value of 1,2,3 or based on a set criteria Windows 10 Formula to return a value of 1,2,3 or based on a set criteria Office 2016
Novice
Formula to return a value of 1,2,3 or based on a set criteria
 
Join Date: Jan 2016
Location: Portsmouth, England
Posts: 18
Highlander01 is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 06-01-2017, 12:59 PM
NBVC's Avatar
NBVC NBVC is offline Formula to return a value of 1,2,3 or based on a set criteria Windows 7 64bit Formula to return a value of 1,2,3 or based on a set criteria Office 2007
The Formula Guy
 
Join Date: Mar 2012
Location: Mississauga, CANADA
Posts: 215
NBVC will become famous soon enoughNBVC will become famous soon enough
Default

Does this work?

=LOOKUP(DATEDIF(TODAY(),L5,"m"),{0,4,6,12},{1,2,3, 4})
Reply With Quote
  #3  
Old 06-01-2017, 01:05 PM
Highlander01 Highlander01 is offline Formula to return a value of 1,2,3 or based on a set criteria Windows 10 Formula to return a value of 1,2,3 or based on a set criteria Office 2016
Novice
Formula to return a value of 1,2,3 or based on a set criteria
 
Join Date: Jan 2016
Location: Portsmouth, England
Posts: 18
Highlander01 is on a distinguished road
Default

WOW! Thank you so much.

I will give it a go tomorrow.

Gordon
Reply With Quote
  #4  
Old 06-01-2017, 01:16 PM
NBVC's Avatar
NBVC NBVC is offline Formula to return a value of 1,2,3 or based on a set criteria Windows 7 64bit Formula to return a value of 1,2,3 or based on a set criteria Office 2007
The Formula Guy
 
Join Date: Mar 2012
Location: Mississauga, CANADA
Posts: 215
NBVC will become famous soon enoughNBVC will become famous soon enough
Default

No Problem.

If the dates in L5 are before today, then switch the L5 and TODAY() parameters...
Reply With Quote
  #5  
Old 06-01-2017, 01:24 PM
Highlander01 Highlander01 is offline Formula to return a value of 1,2,3 or based on a set criteria Windows 10 Formula to return a value of 1,2,3 or based on a set criteria Office 2016
Novice
Formula to return a value of 1,2,3 or based on a set criteria
 
Join Date: Jan 2016
Location: Portsmouth, England
Posts: 18
Highlander01 is on a distinguished road
Default

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
Reply With Quote
  #6  
Old 06-01-2017, 01:56 PM
NBVC's Avatar
NBVC NBVC is offline Formula to return a value of 1,2,3 or based on a set criteria Windows 7 64bit Formula to return a value of 1,2,3 or based on a set criteria Office 2007
The Formula Guy
 
Join Date: Mar 2012
Location: Mississauga, CANADA
Posts: 215
NBVC will become famous soon enoughNBVC will become famous soon enough
Default

Like this:

=LOOKUP(DATEDIF(L5,TODAY(),"m"),{0,4,6,12},{1,2,3, 4})
Reply With Quote
  #7  
Old 06-01-2017, 02:18 PM
Highlander01 Highlander01 is offline Formula to return a value of 1,2,3 or based on a set criteria Windows 10 Formula to return a value of 1,2,3 or based on a set criteria Office 2016
Novice
Formula to return a value of 1,2,3 or based on a set criteria
 
Join Date: Jan 2016
Location: Portsmouth, England
Posts: 18
Highlander01 is on a distinguished road
Default

Excellent, thank you.
Reply With Quote
  #8  
Old 06-02-2017, 01:17 PM
Highlander01 Highlander01 is offline Formula to return a value of 1,2,3 or based on a set criteria Windows 10 Formula to return a value of 1,2,3 or based on a set criteria Office 2016
Novice
Formula to return a value of 1,2,3 or based on a set criteria
 
Join Date: Jan 2016
Location: Portsmouth, England
Posts: 18
Highlander01 is on a distinguished road
Default

Quote:
Originally Posted by NBVC View Post
Like this:

=LOOKUP(DATEDIF(L5,TODAY(),"m"),{0,4,6,12},{1,2,3, 4})
I tried this today and hey presto. Thank you very much.

Appreciated!

Gordon
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula to return a value of 1,2,3 or based on a set criteria Return Max Text or Number Value based on Criteria kwilson307 Excel 1 04-20-2017 05:07 AM
Formula to return a value of 1,2,3 or based on a set criteria 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
Formula to return a value of 1,2,3 or based on a set criteria 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

Other Forums: Access Forums

All times are GMT -7. The time now is 09:11 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft