Forum Discussion

Chelsea Wright's avatar
Chelsea Wright
Copper Contributor
May 19, 2017

IFS Function adding changing results from .045 to 1.045

Below is the function I'm using to track the overall weight loss percentage of members over a 12 week period. The problem is that, say the result for (D3-E3)/D3 is .045, or 4.5%, the result of the ifs function shows 1.045, or 104.5%. Please advise on how to fix this issue. Thank you in advance.

 

IF(Q3>1,(D3-Q3)/D3,(D3-P3)/D3)+IFS(Q3>0,(D3-Q3)/D3,P3>0,(D3-P3)/D3,O3>0,(D3-O3)/D3,N3>0,(D3-N3)/D3,M3>0,(D3-M3)/D3,L3>0,(D3-L3)/D3,K3>0,(D3-K3)/D3,J3>0,(D3-J3)/D3,I3>0,(D3-I3)/D3,H3>0,(D3-H3)/D3,G3>0,(D3-G3)/D3,F3>0,(D3-F3)/D3,E3>0,(D3-E3)/D3)

Formulas & Functions
Need Help

1 Reply

Sort By
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    May 19, 2017

    Hi Chelsea,

     

    In your expression you have sum of two functions, IF and IFS. IFS itself shall give correct result, i guess +1 is added by IF. Your re-written expression looks like

    =IF(Q3>1,
   (D3-Q3)/D3,
   (D3-P3)/D3
 ) +
 IFS(
        Q3>0,(D3-Q3)/D3,
        P3>0,(D3-P3)/D3,
        O3>0,(D3-O3)/D3,
        N3>0,(D3-N3)/D3,
        M3>0,(D3-M3)/D3,
        L3>0,(D3-L3)/D3,
        K3>0,(D3-K3)/D3,
        J3>0,(D3-J3)/D3,
        I3>0,(D3-I3)/D3,
        H3>0,(D3-H3)/D3,
        G3>0,(D3-G3)/D3,
        F3>0,(D3-F3)/D3,
        E3>0,(D3-E3)/D3
 )

     

     

Resources