I need to add something onto the formula...Can anyone help?

[Nicole]

=SUM(S5:X5)+IF(Q5<R5,R5-Q5,0)+IF(R5<Q5,R5-Q5,0)

I want to add if total is <=0 then =0

 

[JoeU2004]

=SUM(S5:X5)+IF(Q5<R5,R5-Q5,0)+IF(R5<Q5,R5-Q5,0)
I want to add if total is <=0 then =0

=MAX(0, SUM(S5:X5)+IF(Q5<R5,R5-Q5,0)+IF(R5<Q5,R5-Q5,0) )

But your two IF() expressions seem odd. Together, they say compute R5-Q5 if
R5 does not equal Q5, otherwise compute zero. But that is exactly what
R5-Q5 does.

I wonder if you want IF(Q5<R5,R5-Q5,0)+IF(R5<Q5,Q5-R5,0). But that is the
same as ABS(Q5-R5).

 

[Nicole]

Hi Joe,

I had some help from my supervisor and I have done part of the formula. What
I am doing is I have -

Stock on Hand, Min.Stock in Warehouse, Stock on order.

Therefore I want a formula to add together the stock on order, taking into
account the min. stock we want and what we already have made. Then if there
is nothing on order but we have more than the stock on had to not put in a
negative number but just stay =0. Can you tell me what you would use? If this
doesn't make sense, I am Happy to email an example.

Thank you for helping Kind Regards,

Nicole

 

[JoeU2004]

What I am doing is I have -
Stock on Hand, Min.Stock in Warehouse, Stock on order.

I want a formula to add together the stock on order, taking into
account the min. stock we want and what we already have made.

I think you are saying that you want to determine stockOnOrder. If
minStockInWarehouse is more than stockOnHand, you want stockOnOrder to be
the additional amount that you need to order. Right?

If I understand that correctly, stockOnOrder is computed by:

MAX(0, minStockInWarehouse - stockOnHand)

If that does not answer your question, feel to send an Excel file (Excel
2003 or earlier) to joeu2004 "at" hotmail.com. In the text of the email,
please let me know what to look at in the Excel file.


----- original message -----

 

[Nicole]

Hi Joe,

Thank you very much for your help, The formula I have used is:

=MAX(0,SUM(S7:X7)+IF(Q7<R7,R7-Q7,0)+IF(R7<Q7,R7-Q7,0)) =9
Without the "if" part, it doesn't take into account the different stock
levels somehow.

SOH Min Stock Stock on order
Stock to be made
26 25 10 9


I apprecitate your help
Nicole