PDA

View Full Version : Planing Formula



Nec11
06-06-2013, 12:05 AM
Hello to all,
I need help in the following attached file.

The story is so:
-I have one client that make for me an Weekly Forecast. (Line 1: "Forecast")
-Weekly he send's to me some quantityes parts. (Line 2: "Received")
The problem:
On the Line 3: "Planned" I need to fill the planned quantity. NOT greather than Forcasted, NOT greather than the sum of good received till today, NOT greather than the difference between Line 3 "Received" and Line 6 "Seded".

I have tryed different formulas but it seems too difficult for my :banghead: brain to find an solution.

Thank you in advance.

Aussiebear
06-06-2013, 12:21 AM
In B5 enter =Min(B2,B3,(B3-B6))

Nec11
06-06-2013, 01:08 AM
Probably I did not made mysef clear...
I need on the line 5 inthead of 0 to find an formulla to ghive me the correct quantity that I can plann it inthead of "0".
The correct quantity can NOT be greather than Forcasted, NOT be greather than the sum of good received till today (today= S"x"), NOT greather than the difference between Line 3 "Received" and Line 6 "Sended".

Aussiebear
06-06-2013, 02:47 AM
I need on the line 5 inthead of 0 to find an formulla to ghive me the correct quantity that I can plann it inthead of "0".
The correct quantity can NOT be greather than Forcasted, NOT be greather than the sum of good received till today (today= S"x"), NOT greather than the difference between Line 3 "Received" and Line 6 "Sended".

When I opened your workbook it became clear as to which lines you were wanting a result in. Did you try the formula offered and in what way does the result differ from what you expected.

The formula offered can be broken down in the following sequence

Since the value in B5 cannot be greater than the value in B2, nor can it be greater than the figure in B3, nor can it be greater than the difference between the value in B3 and B6, I simply used Min(B2,B3,(B3-B6)) to find the lowest number of those three factors.

Therefore B5=Min(B2,B3,(B3-B6)) becomes B5 = Min(1500,800,(800-658)) which in turn becomes B5=Min(1500,800,142) therefore B5 =142

Nec11
06-06-2013, 04:21 AM
Your formula is working, no doubt abt this.

Can you open the attached file maybe it will ghive you more informations about the problem.

Regards

Aussiebear
06-06-2013, 03:12 PM
The data you have shown in the workbook is some what confusing, however it appears to me that you are trying to;

Forecast production by working out the difference between Stock on Hand and the production rate, and

Keep an accurate idea of the Stock on Hand by adding the pieces received, minus the pieces produced, and

Work out the maximum pieces that can be produced per day by calculating the forecast figure or the total stock on hand and using the lessor value of the two.

Is this correct?

Nec11
06-06-2013, 10:34 PM
Yes your theory is correct and better explained than me :)

Aussiebear
06-07-2013, 01:48 AM
Ok then try the following file. Others may be able to solve it better.

p45cal
06-07-2013, 04:28 PM
I'm not sure I've understood fully, so I've had a go by creating a variant of Aussiebear's solution.
My Can Produce row is what I took to mean by Planned, and the Would like to produce row the same as your Forecast - they were just easier for me to get my head round (if I've got them right, that is).
Anyway have a play with the Concept sheet and see if it gives you what you want.
I've protected the sheet (no password) so you can only enter data into the correct cells.
The formulae in rows 4 and 5 will only show a value if one or more of the cells in rows 2, 3 and 6 of the same column has a value in, otherwise they will be blank.

Nec11
06-08-2013, 02:29 AM
From an quick view the secon variant it looks better.

I will test it and let you know asap.

Regards & Thank you!