If formula not giving expected result

[Island Gryl]

=IF(U3>0,U3-H3,U3) Result is -33 Expecting 0

Where U3 is 0 (the result of a Vlookup with no result)
Where H3 is 33


This formula works when U3 is not zero and returns this negative result when it is.
I'd like to have it give me the difference of U - H (U is always greater than H except when it's zero)

Hoping this is simple enough that my skipping the example create is ok.

Appreciate the practiced eye!

 

[6StringJazzer]

What is the formula in U3? How do you know it's exactly 0? Is it possible that the value is something like 0.00000001 but you are not showing the decimal part in the cell?

 

[StephenCrump]

I agree with @6StringJazzer's comment - your description doesn't add up.

But it sounds like you want: =MAX(0,U3-H3)?

 

[rlv01]

Here is how it works for me. What is it that you are expecting to be different?

URL_Maker.xlsm
	H													U	V
3	33													1	-32
4	33													0	0
5	33													-1	-1
Sheet2

Cell Formulas
Range	Formula
V3:V5	V3	=IF(U3>0,U3-H3,U3)

 

[Island Gryl]

In reply to first question, what is the formula in U =IFERROR(VLOOKUP(K3, Orders!$B$2:$L$990, 11, 0),"0")
It is getting an email address in K3 and then looking for a match in the Orders Tab, if there is a match place the value in U (which is currency USD) if no match put 0.
So in answer to the second question, it shouldn't be anything other than a zero...

In answer to Stephen, I kinda new something like that was in there but didn't know how to get there. That is a very simple approach and it works!

The whole effort is to get a total of currency exchange on Months Sales Orders as reported on our Shopify Platform (we are CDN based so Shopify reports everything in CDN) we have recently opened a US$ deposit account for our US Orders so I need to convert back these orders that are paying out in USD to balance the payouts and record the FX.

Thanks to Stephen for the elegant simple fix!

 

[StephenCrump]

That explains the funny result. "0" (text) is not the same as 0 (numeric).

So if U3 = "0", then the test U3>0 in your formula below will be TRUE:

=IF(U3>0,U3-H3,U3)

And U3 - H3 will coerce the U3 value "0" to numeric 0.

 

[6StringJazzer]

So use this

=IFERROR(VLOOKUP(K3, Orders!$B$2:$L$990, 11, 0),0)