h2g2 The Hitchhiker's Guide to the Galaxy: Earth Edition

Seeing as how I received such good help before, I now have another query about spreadsheets in Excel.

I'm putting together a spreadsheet for stock control. I have a column for stock in hand and a column for stock required according to predicted demand. The column for ordering stock is simply "stock required - stock in hand). However, where I have an excess of stock this gives a negative figure. Is there a way for me to format the cells so that negative figures come up as zero?

Use a formula something like:

if(stockrequired - stockinhand > 0,stockrequired - stockinhand,0)


Geggs

PS.

Is there also a way to round figures *up*?

i.e. If I need 4.2 cases, I need to order 5 cases.

That way, if the 'stockrequired - stockinhand' sum is greater than zero, then it will display the result of the sum, otherwise it will display a zero.


Geggs

I'd use the 'ROUNDUP' function myself

So, putting it all together we get:

=roundup(if(stockrequired - stockinhand > 0,stockrequired - stockinhand,0),0)


Geggs

Thankyou thankyou thankyou !!!!

Great work guys. Have a virtual on me