Home

IFS Function adding changing results from .045 to 1.045

Highlighted
Chelsea Wright
Occasional Visitor

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)

1 Reply

Re: IFS Function adding changing results from .045 to 1.045

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
 )

 

 

Related Conversations
IFERROR - HLOOKUP- IF formula error
data24365 in Formulas and Functions on
14 Replies
IF Statement with Networkdays
R S in Formulas and Functions on
4 Replies
Add 5 if a cell contains text "yes"
Titchard Family in Formulas and Functions on
8 Replies
Comparing two ranges of cells in an if function
Joseph Assaf in Excel on
6 Replies