Multiple IF Statements, w/ calculation and ABS formula

[NinaE_11]

Hello!

I'm hoping to find some guidance how to incorporate all of the below attributes into one formula. I have stats for Year1, and Year2 that I divide, truncate to avoid any outliers, and in this case, need to find how to incorporate an absolute va(((lue of a result.

Example earnings estimates:
Year1 Year2
-$0.81 -$0.39
Using these stats to calculate y/y growth, my current formula that also incorporates truncating at 70 / -70 to remove outliers is:
=IF(((Year2/Year1))-1>0.7,0.7, IF((Year2/Year1)-1)<-0.7,-0.7,(Year2/Year1)-1)))*100

However, even though both years are negative, Year2 shows an improvement, so we want to show a positive growth result.
=(-$0.39/-$0.81)-1 = -0.52; which is not ideal and managers want to see a positive number to reflect the 'improvement nature".

How can I incorporate an ABS condition into the above IF formula?

See enclosed scenarios for added depth.

Thank you so much for any insights!

 

[Vogateer]

Is this what you want? I changed the truncation formulas, too, but I'm not sure I understand how you want them truncated.

=IF((Year2-Year1)/ABS(Year1)-1>0.7,0.7, IF((Year2-Year1)/ABS(Year1)<-0.7,-0.7,(Year2-Year1)/ABS(Year1))*100)

 

[Tetra201]

Maybe

=SIGN(B2-A2)*MIN(0.7,ABS(B2/A2-1))

 

[NinaE_11]

Is this what you want? I changed the truncation formulas, too, but I'm not sure I understand how you want them truncated.

=IF((Year2-Year1)/ABS(Year1)-1>0.7,0.7, IF((Year2-Year1)/ABS(Year1)<-0.7,-0.7,(Year2-Year1)/ABS(Year1))*100)

Thank you! I did end making some additional edits to this formula to get decimal places corrected, etc; but the meat of the question was answered by your addition of the ""ABS(Year1)"" inclusion in the denominator.

Thank you again!

 

[Joe4]

NinaE_11,
Please note that when marking a post as the solution, please mark the original post containing the solution, not your own post acknowledging some other post was the solution.
You would only mark your own post as the solution if you came up with the solution yourself, and you posted the solution you came up with.

Thanks

 

[NinaE_11]

NinaE_11,
Please note that when marking a post as the solution, please mark the original post containing the solution, not your own post acknowledging some other post was the solution.
You would only mark your own post as the solution if you came up with the solution yourself, and you posted the solution you came up with.

Thanks

Got it. I just didn't want someone Googling this solution and duplicating it exactly, because there was added tweaks to be made. Thanks!

 

[Joe4]

Got it. I just didn't want someone Googling this solution and duplicating it exactly, because there was added tweaks to be made. Thanks!

Then post the exact formula you used (tweaks included), and you can mark that as the solution.

 

[NinaE_11]

Solution:

=IF(((Y2-Y1)/(ABS(Y1)))>0.7,0.7,IF((Y2-Y1)/ABS(Y1)<-0.7,-0.7,(Y2-Y1)/ABS(Y1)))*100