1. ### Welcome to Chandoo.org Forums. Short message for you

Hi Guest,

Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

Yours,
Chandoo
2. Hi All

Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

Post Spam and you Will Be Deleted as a User

Hui...

3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

# Replacing a formula value if it is negative with another cell value

Discussion in 'Ask an Excel Question' started by Matt_Straya, May 16, 2018.

1. ### Matt_StrayaNew Member

Messages:
28
Hi,
I have this formula
Code (vb):
=IFERROR((C3-D3)/B3,0)*E3
that may result in a negative number (\$). If it does I want to display the value in B3.

Grateful for any ideas

File size:
8.7 KB
Views:
6
2. ### GraH - GuidoWell-Known Member

Messages:
751
Quick and dirty, perhaps this one
=IF(IFERROR((C3-D3)/B3,0)*E3<0,B3, IFERROR((C3-D3)/B3,0)*E3)

But I don't like it very much. I'm sure there is a more elegant one possible. Can't look at your data for the moment to evaluate it.
3. ### GraH - GuidoWell-Known Member

Messages:
751
Not a lot of data in your file.
=IF((C3-D3)/B3*E3<0,B3,IFERROR((C3-D3)/B3*E3,0)
Thomas Kuriakose likes this.
4. ### bosco_yipExcel Ninja

Messages:
1,851
Or,

=IF(D3>C3,B3,IFERROR((C3-D3)/B3,0)*E3)

Regards
Bosco
Matt_Straya and Thomas Kuriakose like this.
5. ### GraH - GuidoWell-Known Member

Messages:
751
or?
=IFERROR(IF(D3>C3,B3,(C3-D3)/B3*E3),0)
Matt_Straya likes this.
6. ### Matt_StrayaNew Member

Messages:
28
Thanks guys! brilliant! Both seem to work