Solved

how to write more than 7 IF statements within a formula

Posted on 2011-09-07
4
168 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
  • 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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

828 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