Add to this formula to say if the answer is less than 0 its Zero?

[tonywatsonhelp]

Hi Everyone,

I have a fairly long formula that I use a lot in a sheet to calculate a value,

I'll not go into to much detail as to what it does as it works fine and I'm just wondering if there is a better solution to my edit to make minus numbers all "0"

so basicly the formula does some adding and taking away and give me the final value so 50+50+50 = 150 etc.
the problem is we can't have minus numbers,

so 50+50+50-200 =-50 but I want it to show as 0

the original formula is just:

"=if($C8="","",if(I_StockTake!$E$2=AV$5,I_StockTake!$E6,AU8)+IF(C_OrdersArrival!AV$1=1,C_OrdersArrival!AV8,0)-IF(C_ItemForecast!AV$1=1,C_ItemForecast!AV8,0))"

and to make it show 0 instead of minus I added another if with the formula repeated,

So:
=if($C8="","",If(if(I_StockTake!$E$2=AV$5,I_StockTake!$E6,AU8)+IF(C_OrdersArrival!AV$1=1,C_OrdersArrival!AV8,0)-IF(C_ItemForecast!AV$1=1,C_ItemForecast!AV8,0))<0,0,if(I_StockTake!$E$2=AV$5,I_StockTake!$E6,AU8)+IF(C_OrdersArrival!AV$1=1,C_OrdersArrival!AV8,0)-IF(C_ItemForecast!AV$1=1,C_ItemForecast!AV8,0)))

but this seams like a long way of doing it, is there a better way to add in if result is less than 0 use 0 ???

any ideas would be a help.


Thanks

Tony

 

[AliGW]

Try this:

=IF($C8="","",MAX(0,IF(I_StockTake!$E$2=AV$5,I_StockTake!$E6,AU8)+IF(C_OrdersArrival!AV$1=1,C_OrdersArrival!AV8,0)-IF(C_ItemForecast!AV$1=1,C_ItemForecast!AV8,0)))

 

[tonywatsonhelp]

Brilliant!

Thank you so much Aligw Works perfect

Tony

 

[AliGW]

No problem! And I learnt something today, too.