Adding 2 IF statements together

[Danielle_LKM]

Hello, I have a system stock report which has some empty cells (depending on the day's movements). The empty cells should stay empty.

I then work out whether we've bought or sold at a profit or loss (I have weekly nominal prices to do this).

It means I end up with 3 key columns, something like this:

1. If the stock movement in purchases cell is empty, don't do the calculation; if there's a quantity, calculate the variance against the nominal buying price.
2. If the stock movement in sales cell is empty, don't do the calculation; if there's a quantity, calculate the variance against the nominal selling price.
3. Add the 2 outcomes together.

I can either have a formula in column 3 like this: =+IF(F5="","",+C5*F5-H5*F5)+IF(K5="","",-H5*K5+M5*K5)
Or I can simply add the cells that contain the result from Calc 1 and the result from Calc 2.

Either way I end up with #VALUE! as my result. How can I calculate my 'profit/loss' on a product type on the above basis?

Many thanks
Danielle

[galvinpaddy]

Hi, Can you load a sample sheet? (with sensitive data removed?)

[Danielle_LKM]

I've tried to upload, can you see it?
Thanks

[galvinpaddy]

Hello, thanks i can see it.
I have some questions though if i may.
Your cells containing formulas, (from my experience, which is not vast, PEMDAS should be used) i thought you had to seperate what parts of your mathematics you wanted to calculate first? for example

=IF(J11="","",-C11*J11+L11*J11)

shows a result of £38.
C11 = £1,875
J11 = 0.1
L11 = £1,500
So, is your formula supposed to work as C11*J11 (first) (then add the result of) L11*J11?

=IF(J11="","",(C11*J11)+(L11*J11))

showing a result of £338?
Or have i seriously mis read what you have already done?

[Danielle_LKM]

Hello again, the formula should read:

=IF(J11="","",-(C11*J11)+(L11*J11))

But the result is still correct, being the £38.

I am seeing what we would have yielded at nominal price, and comparing it to our actual sales values.

I sold Product 5 @ £1,500/tonne, and should have sold it at £1,875/tonne. That's an unfavourable result of £375/tonne, but I only sold 0.1 of a tonne, so it's £37.50 negative.

I hope that helps.
Thank you.

[brokenbiscuits]

How about:

=IF(H5<>"",H5,0)+IF(M5<>"",M5,0)

?

[Danielle_LKM]

How about:

=IF(H5<>"",H5,0)+IF(M5<>"",M5,0)

?

That is IT!!!!

Thank you so much!

[ben_hensel]

=IF(H5<>"",H5,0)+IF(M5<>"",M5,0)

Isn't this equivalent to
=SUM(H5,M5)
?

[Danielle_LKM]

Isn't this equivalent to
=SUM(H5,M5)
?

That didn't work, I think because I've specifically got empty cells as empty as opposed to zero value. But I'm not sure, I just know it returned error msgs.