# 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!  Register To Reply

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  Register To Reply

3. thanks for the quick response!

=value did work  Register To Reply

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)))  Register To Reply