Solved

how to write more than 7 IF statements within a formula

Posted on 2011-09-07
4
170 Views
Last Modified: 2012-08-13
I got an error message when I reach August.  I heard the limitation is 7 IF statements within one formula.  Is there any other way I can include all 12 months in one formula?

IF(TRIM($C21)="January",1,IF(TRIM($C21)="February",2,IF(TRIM($C21)="March",3,IF(TRIM($C21)="April",4,IF(TRIM($C21)="May",5,IF(TRIM($C21)="June",6,IF(TRIM($C21)="July",7,0)))))))
0
Comment
Question by:jjxia2001
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 36498327
Use this formula:

=MATCH($C21,{"January","February","March","April","May","June","July","August","September","October","November","December"},0)

Kevin
0
 
LVL 81

Assisted Solution

by:zorvek (Kevin Jones)
zorvek (Kevin Jones) earned 350 total points
ID: 36498337
Or this one:

=MONTH(VALUE("1 "&$C21))

Kevin
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 36498341
You can't have more than seven nested functions which is what you are encountering with the IF function formula.

Kevin
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 150 total points
ID: 36498351
If you have just the month as text then this should do it

=MONTH(1&$C21)

regards, barry
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question