Excel IF/OR formula not working.

Attached is a sample spreadsheet where I have an IF/OR formula that's not working.

I simply want to return a value based on today's month, which varies in different months.

No arrays, please ... I just can't figure out why the formula is not working.

Thanks!
Tim JackoboiceOwnerAsked:
Who is Participating?
 
Subodh Tiwari (Neeraj)Connect With a Mentor Excel & VBA ExpertCommented:
Please try this....
In A1
=IF(ISNUMBER(MATCH(MONTH(TODAY()),{7,8,9},0)),120,IF(ISNUMBER(MATCH(MONTH(TODAY()),{1,2,3,4},0)),250,180))

Open in new window

For more details, refer to the attached.
Month-Return.xlsx
0
 
Rob HensonFinance AnalystCommented:
No attachment.

In the meantime, maybe you can use:

=CHOOSE(MONTH(TODAY()),"option 1","option 2","option 3","option 4","option 5","option 6","option 7","option 8","option 9","option 10","option 11","option 12")

Thanks
Rob H
0
 
Tim JackoboiceOwnerAuthor Commented:
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Tim JackoboiceOwnerAuthor Commented:
(Sorry about that!)
0
 
Rob HensonFinance AnalystCommented:
Each of the options for the OR statement have to be specified:

=IF(OR (MONTH(TODAY())=6,MONTH(TODAY())=7,MONTH(TODAY())=8),120,IF(......etc

Thanks
Rob
0
 
Tim JackoboiceOwnerAuthor Commented:
Thanks so much to everybody. I chose the shortest and most concise formula.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome. Glad to help.
0
 
Rob HensonFinance AnalystCommented:
Even shorter:

=IF(MONTH(TODAY())<=4,250,IF(AND(MONTH(TODAY())>=7,MONTH(TODAY())<=9),120,180))

In other words, specified amounts are:
1 to 4 = 250
7 to 9 = 120
Everything else (5, 6 10, 11 & 12)= 180
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.