Solved

# how to write more than 7 IF statements within a formula

Posted on 2011-09-07
167 Views
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
Question by:jjxia2001
• 3

LVL 81

Expert Comment

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

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

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

Kevin
0

LVL 81

Expert Comment

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

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

Question has a verified solution.

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

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original souâ€¦
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns overâ€¦
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.