# Multiple If statements

1. ## Multiple If statements

I believe you are only allowed to make 7 if statements and i need 13 so i did some research and tried the concatenate function. when i did this it worked however i am not able to format the solution.
here is the code:

=CONCATENATE(IF(H5="feb",GETPIVOTDATA("# of Returns",\$G\$7)/Q5,0),IF(H5="Mar",GETPIVOTDATA("# of Returns",\$G\$7)/Q6,0),IF(H5="(all)",GETPIVOTDATA("# of Returns",\$G\$7)/Q16,0),IF(H5="apr",GETPIVOTDATA("# of Returns",\$G\$7)/Q7,0),IF(H5="may",GETPIVOTDATA("# of Returns",\$G\$7)/Q8,0),IF(H5="jun",GETPIVOTDATA("# of Returns",\$G\$7)/Q9,0),IF(H5="jul",GETPIVOTDATA("# of Returns",\$G\$7)/Q10,0),IF(H5="aug",GETPIVOTDATA("# of Returns",\$G\$7)/Q11,0), IF(H5="sep",GETPIVOTDATA("# of Returns",\$G\$7)/Q12,0),IF(H5="oct",GETPIVOTDATA("# of Returns",\$G\$7)/Q13,0), IF(H5="nov",GETPIVOTDATA("# of Returns",\$G\$7)/Q14,0), IF(H5="dec",GETPIVOTDATA("# of Returns",\$G\$7)/Q15,0))

for example for may i get: 00.000757043205537230000000000

i am trying to get it returned as a percentage but formatting the cell does not work.

any ideas on how to return it as a percentage, also is there any easier way to do that many if statements without using the concatenate function?

Appreciate the help!

2. Good evening 00T

There are several workarounds to handle the 7 nested if limit. Unfortunately, CONCATENATE is a text function, so your result is returned as a text string which means you can't add or format your result. You could try this :

=VALUE(CONCATENATE(IF(H5="feb",GETPIVOTDATA("# of Returns",\$G\$7)/Q5,0),IF(H5="Mar",GETPIVOTDATA("# of Returns",\$G\$7)/Q6,0),IF(H5="(all)",GETPIVOTDATA("# of Returns",\$G\$7)/Q16,0),IF(H5="apr",GETPIVOTDATA("# of Returns",\$G\$7)/Q7,0),IF(H5="may",GETPIVOTDATA("# of Returns",\$G\$7)/Q8,0),IF(H5="jun",GETPIVOTDATA("# of Returns",\$G\$7)/Q9,0),IF(H5="jul",GETPIVOTDATA("# of Returns",\$G\$7)/Q10,0),IF(H5="aug",GETPIVOTDATA("# of Returns",\$G\$7)/Q11,0), IF(H5="sep",GETPIVOTDATA("# of Returns",\$G\$7)/Q12,0),IF(H5="oct",GETPIVOTDATA("# of Returns",\$G\$7)/Q13,0), IF(H5="nov",GETPIVOTDATA("# of Returns",\$G\$7)/Q14,0), IF(H5="dec",GETPIVOTDATA("# of Returns",\$G\$7)/Q15,0)))

which will attempt to force the result back as a number. If that doesn't work look at Chip Pearson's workaround for 7 nested ifs here :

http://www.cpearson.com/excel/nested.htm

HTH

DominicB

3. thanks for the quick response!

=value did work

4. Originally Posted by 00T
also is there any easier way to do that many if statements without using the concatenate function?
Try

=GETPIVOTDATA("# of Returns",\$G\$7)/IF(H5="(all)",Q16,INDEX(Q4:Q15,MONTH("1 "&H5)))

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1