Excel formula to show $0.00 if negative number.

ssblue
ssblue used Ask the Experts™
on
I need the following formula to show $0.00 if it is a negative number, otherwise perform calculation.

="Avoided Spend w/ Reference = "&TEXT('SSG 2017 Orders'!AT2,"[$$ -409]#,##0.00")
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Technology and Business Process Advisor
Most Valuable Expert 2013
Commented:
Use If - I believe you want this:

="Avoided Spend w/ Reference = "&TEXT(If('SSG 2017 Orders'!AT2<0,0,'SSG 2017 Orders'!AT2),"[$$ -409]#,##0.00")
Top Expert 2016

Commented:
Hi,

pls try
="Avoided Spend w/ Reference = "&TEXT(A3,"[$$ -409]#,##0.00;""$ 0.00""") 

Open in new window

Regards
ssblueCoordinator

Author

Commented:
Lee, yours shows >0

Rgonzo, yours shows $0.00 all the time - it should show the calculation if it is a positive number.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Lee W, MVPTechnology and Business Process Advisor
Most Valuable Expert 2013

Commented:
Had a typo - I corrected, try again.
Top Expert 2016

Commented:
corrected code
="Avoided Spend w/ Reference = "&TEXT('SSG 2017 Orders'!AT2,"[$$ -409]#,##0.00;""$0.00""") 

Open in new window

ssblueCoordinator

Author

Commented:
Thanks
How about

="Avoided Spend w/ Reference = "&IF('SSG 2017 Orders'!AT2<0,"0.0",TEXT('SSG 2017 Orders'!AT2,"[$$ -409]#,##0.00"))

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial