+ Reply to Thread
Results 1 to 4 of 4

Multiple If statements

  1. #1
    Registered User
    Join Date
    12-14-2006
    Posts
    4

    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. #2
    Forum Moderator dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,866

    Smile

    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
    Please familiarise yourself with the rules before posting. You can find them here.

  3. #3
    Registered User
    Join Date
    12-14-2006
    Posts
    4
    thanks for the quick response!

    =value did work

  4. #4
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    Quote 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)))
    Last edited by daddylonglegs; 03-07-2007 at 07:03 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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