May 19 2017
10:35 AM
- last edited on
Jul 25 2018
09:37 AM
by
TechCommunityAP
May 19 2017
10:35 AM
- last edited on
Jul 25 2018
09:37 AM
by
TechCommunityAP
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)
May 19 2017 12:38 PM
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 )