Cell recognize positive or negative value then adds certain texts to the 2 possibilities

[determinedtoexceed]

Dear professionals,

when a cell result in ANY positive value, example such as "10%", I would like the very same cell to display as "Underestimate 10%"

on the other hand a cell result in ANY negative value, such as "-5%", i would like the very same cell to display as "Overestimate 5%"

Please help out and thanks for your time.

Best wishes.

 

[Norie]

You could try this custom format.

"Overestimate" 0%; "Underestimate" 0%

 

[Yongle]

... and

.. the 3rd element formats 0%

"Underestimate" 0%; "Overestimate" 0%;"Spot on!"

... to add colour to an element ..

[Red]"Underestimate" 0.00%;[Blue] "Overestimate" 0.00%;"Spot on!"

 

[determinedtoexceed]

Thanks for the help guys, so this is not doable through a formula

 

[Yongle]

Use this method - but apply your current formula to get the correct percentage value considered
(in my example the percentage is a simple division of 2 cellls)

=IF(A1/B1=0,"Spot on",IF(A1/B1>0,"Underestimate "&100*A1/B1&"%","Overestimate "&-100*A1/B1 & "%"))

I prefer custom format method to the messy formula in this post

You may want to round the values, which makes it even messier

=IF(A8/B8=0,"Spot on",IF(A8/B8>0,"Underestimate "&ROUND(100*A8/B8,0)&"%","overestimate "&ROUND(-100*A8/B8,0) & "%"))

 

[Yongle]

if you performed the percentage calculation in another cell (D1 in my example) first, then the formula could be simplified

=IF(D1=0,"Spot on",IF(D1,"Underestimate "&TEXT(D1,"0%"),"overestimate "&TEXT(D1,"0%")))

 

[Yongle]

My previous post should have been ...
=IF(D1=0,"Spot on",IF(D1>0,"Underestimate "&TEXT(D1,"0%"),"overestimate "&TEXT(D1,"0%")))

 

[Rick Rothstein]

Thanks for the help guys, so this is not doable through a formula

Assuming your value (percentage) is in cell A1, give this formula a try...

=TEXT(A1,"""Overestimate"" 0%;""Underestimate"" 0%;""Spot On""")

 

[determinedtoexceed]

thank you for the trouble Yongle,
the if formula is very useful, replacing "D1" with original formula can show result in 1 single cell

 

[determinedtoexceed]

Thank you Rick, so simple