Help constructing formula...

Matty

Well-known Member
Joined
Feb 17, 2007
Messages
3,717
Hello All,

I am having some trouble constructing a formula, and I'm hoping someone here will be able to help me.

Let's take the following table:

<TABLE style="WIDTH: 338pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=449 border=0 x:str><COLGROUP><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4790" width=131><COL style="WIDTH: 40pt; mso-width-source: userset; mso-width-alt: 1938" span=6 width=53><TBODY><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 98pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=131 height=17></TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 40pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=53>Week*1</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 40pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=53>Week*2</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 40pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=53>Week*3</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 40pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=53>Week*4</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 40pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=53>Week*5</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 40pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=53>Week*6</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Production</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 40pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=53 x:num>0</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 40pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=53 x:num>50</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 40pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=53 x:num>0</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 40pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=53 x:num>0</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 40pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=53 x:num>0</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 40pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=53 x:num>50</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 98pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=131 height=17>Demand</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 40pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=53 x:num>10</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 40pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=53 x:num>15</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 40pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=53 x:num>12</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 40pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=53 x:num>8</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 40pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=53 x:num>16</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 40pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=53 x:num>20</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Stock</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>30</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num x:fmla="=B4-B3+B2">20</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num x:fmla="=C4-C3+C2">55</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num x:fmla="=D4-D3+D2">43</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num x:fmla="=E4-E3+E2">35</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num x:fmla="=F4-F3+F2">19</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Desired Cover (Days)</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>28</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>28</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>28</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>28</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>28</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>28</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ebe9ed; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ebe9ed; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Desired Stock</TD><TD style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent" align=right x:num>45</TD><TD style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">?</TD><TD style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">?</TD><TD style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">?</TD><TD style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">?</TD><TD style="BORDER-RIGHT: #ebe9ed; BORDER-TOP: #ebe9ed; BORDER-LEFT: #ebe9ed; BORDER-BOTTOM: #ebe9ed; BACKGROUND-COLOR: transparent">?</TD></TR></TBODY></TABLE>

I would like a formula to calculate, based on forward Demand, how much stock I need to keep me at the Desired Stock level.

I have manually entered the first figure of 45, which, as the Desired Cover in Days equals 28, it means the Desired Stock is the total of Week 1, Week 2, Week 3 and Week 4's Demand.

I wonder if someone can assist me with this, please?

Thanks,

Matty
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
I am confused! You have presented the case as consisting only of the sum of the demand for the week in question, plus the following three weeks. If so, then neither production nor "in stock" would seem to have any bearing. I would think that, for the first week, the "desired stock" would be 45 (for the four weeks) minus the 30 presently in stock, so,15 would be my answer, not 45. Please explain.
 
Upvote 0

Matty

Well-known Member
Joined
Feb 17, 2007
Messages
3,717
Hello RalphA, and thanks for your interest.

For the value I am trying to calculate by means of a formula, neither Production or Stock has any bearing on the result.

I simply want to understand what my stock figure needs to be based on the forward Demand that exists, taking into account the desired cover in days.

For example, based on a desired cover of 28 days, Week 2 would be 51. However, if my desired cover was 15 days (at Week 2), then the desired stock figure would be 28.14 (15+12+1.14), where 15 is the demand for Week 2 (7 days' worth), 12 is the demand for Week 3 (7 days' worth) and 1.14 is 1 day's worth of demand from Week 4.

Is what I am trying to do now a bit clearer?

Thanks,

Matty
 
Upvote 0

Matty

Well-known Member
Joined
Feb 17, 2007
Messages
3,717
Hello Everyone,

I'm still stumped with this... can anyone point me in the right direction, please?

I recall Domenic helping me with something similar before...

Thanks in advance.

Matty
 
Upvote 0

MorganO

Active Member
Joined
Nov 21, 2006
Messages
483
It would seem that if your desired cover was a static value (in your case 28) this would be a fairly trivial exercise.

For example if you table starts in Cell A1, then:

in cell B6 place: =SUM(B3:E3)

This can then be placed in each cell to the right to continue the desired stock calculations. even with a static value that is not a even multiple of a week this is very simple to accomplish

If, as you note you want the desired cover days to be variable, this becomes much more complicated adventure. I personally would approach it with a User Designed VBA function, but there might be some ingenious way to do this with existing worksheet functions. I am not conversant enough with them to assist in that regards.

Let me know where you are heading and I will continue to assist.

Owen
 
Upvote 0

Matty

Well-known Member
Joined
Feb 17, 2007
Messages
3,717
Hello Owen,

Thanks for responding.

You're quite correct: if the desired cover was a static figure, this would be quite a straight forward thing to do, but the desired cover is not static, and can be different from one week to the next...

Whilst I recognise that a UDF is one way of approaching this, I really want to go down the Worksheet function route, but I'm struggling to put something together that gives me exactly what I need.

I'm hoping one of the formula gurus out there can come up with something that achieves the desired result.

Thanks,

Matty
 
Upvote 0

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Assuming your demand figures for weeks 1 to 6 are in B3:G3 and corresponding "Desired Cover (Days)" in B5:G5 then try this formula in B6 copied across

=IF(B5=0,0,SUM(OFFSET(B3,0,0,1,CEILING(B5/7,1)))-OFFSET(B3,0,INT(B5/7))*MOD(-B5,7)/7)
 
Last edited:
Upvote 0

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
.......a small improvement perhaps.....

=IF(B5<7,0,SUM(OFFSET(B3,0,0,1,INT(B5/7))))+OFFSET(B3,0,INT(B5/7))/7*MOD(B5,7)
 
Upvote 0

Matty

Well-known Member
Joined
Feb 17, 2007
Messages
3,717
Fantastic barry houdini!

I'm keen to understand how this formula works. If you have time, can you help me to understand how it's achieving the result?

Many thanks,

Matty
 
Upvote 0

MorganO

Active Member
Joined
Nov 21, 2006
Messages
483
Harry, that certainly is impressive!
I've decided to break it down for myself to see how it works - it's easy understanding the concept now that I've seen it done!

The first section:
IF(B5<7,0,SUM(OFFSET(B3,0,0,1,INT(B5/7))))
returns the sum of the proper number of full weeks that need to be counted based on the number of cover days. The OFFSET function is the key here. It is used to return a range of values based on this number of weeks. The sum function then sums that range to return a count of the full weeks value.

The second section:
+OFFSET(B3,0,INT(B5/7))/7*MOD(B5,7)
returns the value of a partial week. The OFFSET function moves to the proper week value that needs to be counted, turns it into a single day value (/7) and then multiplies it by the number of needed days (*MOD(B5,7)

This example has taught me something new that I can now use in my own work. Thanks!

Owen
 
Upvote 0

Forum statistics

Threads
1,190,807
Messages
5,983,026
Members
439,813
Latest member
monvarona

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
Top