Help with formula for order forcasting

Permagrin

New Member
Joined
Jul 1, 2009
Messages
12
mqFfl4W.png


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. :oops:


Thanks everyone.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top