Help with formula for order forcasting

[Permagrin]

Hello all.

I am getting a bit frustrated with this one.

This is a sheet used to calculated the number of laptops/desktops to procure for the next quarter.

I need a formula for I4 and then I can just copy it to I5.

Basically, we need to order the right number of devices and have a buffer of 10 devices on top of that.

Lets say there are 50 available and 30 new headcount coming onboard. That would be a zero order since there is enough available to deploy and still have the 10+ buffer.

Now lets say there are 20 available and the forecast is also 20, then the order number should be 30 to account for the headcount plus buffer.

Now, lets say there are 5 available but the forecast is zero. This would make the number 5 to get up to the buffer level.

One more example, 10 available, forecast of 20, so an order of 20.

Im sure this will be trivial for the experts here but I am stuck. The formulas I am coming up with arent working as I would like.

The formula needs to reference G4 so the buffer can be adjusted as needed.

Please help before I smash my face into the keyboard.


Thanks everyone.

 

[dreid1011]

How about this:

=MAX(H4-F4+10,0)

Also, I think you misstated this part:

Now lets say there are 20 available and the forecast is also 20, then the order number should be 30 to account for the headcount plus buffer.

Based on your logic and other examples, I would have expected to order 10.

 

[Permagrin]

Thank you so much that did the trick.

I have to learn all about the MAX function now.

And yes, I did misstate that example. Good catch.

 

[dreid1011]

You're welcome. Glad that is working for you. You mentioned it referencing G4, I suppose you could just change the +10 to +G4 if that amount ever changes.