Formula development help

[mech contractor]

[TABLE="width: 500"]
<TBODY>[TR]
[TD]Total Lin Ft[/TD]
[TD]Wk-1[/TD]
[TD]Wk-2[/TD]
[TD]Wk-3[/TD]
[TD]Wk-4[/TD]
[TD]Wk-5[/TD]
[TD]Wk-6[/TD]
[TD]Production LF Over Under[/TD]
[/TR]
[TR]
[TD]300[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]400[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]200[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1120[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]I just can’t seem to develop a formula for this; any help would be much appreciated.
<o> </o>
I am trying to develop a production / installation tracking sheet given a total linear footage of material to be installed in 6 weeks.
<o> </o>
The formula will need to be based on installation footages, wk-1, then Wk-2 up to 6 weeks. Based on the average linear feet installed each week I need the productivity column to indicate given the averages each week what is the linear footage that will be either ahead or behind at 6 weeks
<o> </o>
Thanks much all
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

 

[Mindpsyche]

Can you provide an example with data and examples of expected results?

 

[mech contractor]

Total Lin FT Feet installed wk-1 Feet installed wk-2 Feet installed wk-3 and so on end wk-6 based on lin feet installed each week will
the production schedule of 600 Lin FT be met at the
end of 6 weeks.
600 180 100

if the avg weekly installation is ahead of schedule
what is the expected plus in this column to be
calculated weekly
if the avg weekly installation is behind schedule
what is the expected minus in this column to be
calculated weekly

 

[mech contractor]

total lin feet week 1 week 2 week 3 week 4 week 5 week 6 Nov

800 260 240 -120


The above is if the production average thru wwek 2 is short.


If production average is above what is needed + footage

 

[Mindpsyche]

I'm sorry but I really can't understand the question. See post 2 of this thread to download Mr Excel HTML Maker which will help you present your problem a lot better.

http://www.mrexcel.com/forum/board-announcements/515787-forum-posting-guidelines.html

 

[mech contractor]

[TABLE="width: 716"]
<COLGROUP><COL style="WIDTH: 19pt; mso-width-source: userset; mso-width-alt: 910" width=26><COL style="WIDTH: 109pt; mso-width-source: userset; mso-width-alt: 5176" width=146><COL style="WIDTH: 33pt; mso-width-source: userset; mso-width-alt: 1564" width=44><COL style="WIDTH: 40pt; mso-width-source: userset; mso-width-alt: 1877" width=53><COL style="WIDTH: 43pt; mso-width-source: userset; mso-width-alt: 2019" width=57><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2190" span=6 width=62><COL style="WIDTH: 148pt; mso-width-source: userset; mso-width-alt: 6997" width=197><COL style="WIDTH: 48pt" width=64><TBODY>[TR]
[TD="width: 26, bgcolor: transparent"][/TD]
[TD="width: 146, bgcolor: transparent"][/TD]
[TD="width: 44, bgcolor: transparent"][/TD]
[TD="width: 53, bgcolor: transparent"][/TD]
[TD="width: 57, bgcolor: transparent"][/TD]
[TD="class: xl77, width: 62, bgcolor: transparent"] [/TD]
[TD="class: xl80, width: 186, bgcolor: transparent, colspan: 3"] Linear Feet Installed[/TD]
[TD="class: xl78, width: 62, bgcolor: transparent"] [/TD]
[TD="class: xl79, width: 62, bgcolor: transparent"] [/TD]
[TD="class: xl71, width: 197, bgcolor: transparent"]At weekly level production Over Under[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl73, bgcolor: #f2f2f2"]Item[/TD]
[TD="class: xl74, bgcolor: #f2f2f2"]ACTIVITY[/TD]
[TD="class: xl74, bgcolor: #f2f2f2"]Size[/TD]
[TD="class: xl74, bgcolor: #f2f2f2"]LF /Qty[/TD]
[TD="class: xl74, bgcolor: #f2f2f2"]Man Hours[/TD]
[TD="class: xl74, bgcolor: #f2f2f2"]Wk- 1[/TD]
[TD="class: xl74, bgcolor: #f2f2f2"]Wk-2[/TD]
[TD="class: xl74, bgcolor: #f2f2f2"]Wk-3[/TD]
[TD="class: xl75, bgcolor: #f2f2f2"]Wk-4[/TD]
[TD="class: xl74, bgcolor: #f2f2f2"]Wk-5[/TD]
[TD="class: xl74, bgcolor: #f2f2f2"]Wk-6[/TD]
[TD="class: xl76, bgcolor: #f2f2f2"]12-Nov[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]1[/TD]
[TD="class: xl72, bgcolor: transparent"]Piping Mechanical Eq Room[/TD]
[TD="class: xl64, bgcolor: transparent"]10"[/TD]
[TD="class: xl64, bgcolor: transparent"]20[/TD]
[TD="class: xl64, bgcolor: transparent"]20[/TD]
[TD="class: xl64, bgcolor: transparent"]1[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"]8"[/TD]
[TD="class: xl64, bgcolor: transparent"]280[/TD]
[TD="class: xl65, bgcolor: transparent"]462 [/TD]
[TD="class: xl64, bgcolor: transparent"]0[/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"]-280[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"]5"[/TD]
[TD="class: xl64, bgcolor: transparent"]300[/TD]
[TD="class: xl65, bgcolor: transparent"]477 [/TD]
[TD="class: xl64, bgcolor: transparent"]0[/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"]-300[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]

 

[Mindpsyche]

Ok thats a lot more clear.

Few questions:

From your table The LF Qty that needs to be installed in 6 weeks is 280?

From the example, it shows that Wk1 is 0 which of course means work has not started and the pending LF Qty is naturally 280.

Could you show an example if Wk1 had some work done. Then what would be the expected numbers in Wk2 through 6.

Say Wk1 has 60. This means that to install 280 LF then you would need 280 / 60 weeks? which is 5 weeks rounded up. Which will leave Wk 6 Free? Am I headed in the right direction?

 

[mech contractor]

if 280' was the total to be installed in 6 weeks it would take an average of 46.67 feet installed per week. If weel one only 40 feet were installed, with 5 weeks left at the pace of work accomplished in week one how many feet short would it be at the end of week 6.


If 60 feet were installed in week one, how many feet ahead of schedule at the pace of work would be ahead by week 6.


If possible to average weekly production, say week one 30 feet installed, week two 110 feet were installed the average production has increased and the result ahead or behing footage at the end of six weeks would change as production quantities change each week.

 

[Snakehips]

mech contractor,

Try this....

It is based on a blank cell if the activity/week are still pending or actual weekly footages from 0 to whatever.
Sheet1

*

A

B

C

D

E

F

G

H

I

J

K

L

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

Wk 1

Wk 2

Wk 3

Wk 4

Wk 5

Wk 6

Over Under

Item

Activity

Size

LF/Qty

Man Hours

*

*

*

*

*

*

*

*

*

8"

*

*

*

-186

*

*

5"

*

*

16.5

*

*

*

*

*

*

*

*

*

*

*

*

<TBODY>
[TD="bgcolor: #cacaca"]1
[/TD]

[TD="bgcolor: #cacaca"]2
[/TD]

[TD="bgcolor: #cacaca"]3
[/TD]

[TD="bgcolor: #cacaca"]4
[/TD]

[TD="align: right"]280
[/TD]
[TD="align: right"]462
[/TD]
[TD="align: right"]37
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]6
[/TD]

[TD="bgcolor: #cacaca"]5
[/TD]

[TD="align: right"]300
[/TD]
[TD="align: right"]300
[/TD]
[TD="align: right"]50
[/TD]
[TD="align: right"]50
[/TD]
[TD="align: right"]66
[/TD]
[TD="align: right"]45
[/TD]

[TD="bgcolor: #cacaca"]6
[/TD]

</TBODY>

Spreadsheet Formulas

Cell Formula L4 =IFERROR((SUMIF(F4:K4,">0",F4:K4)/COUNTA(F4:K4)*6)-D4,"") L5 =IFERROR((SUMIF(F5:K5,">0",F5:K5)/COUNTA(F5:K5)*6)-D5,"") <TBODY> </TBODY>

<TBODY>
</TBODY>

Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4

Hope that helps.

 

[Mindpsyche]

Ok think I might have something. Your first 2 conditions are straight forward but your third condition is tricky.

Excel 2003
	A	B	C	D	E	F	G	H	I	J	K	L
1						Lineaire Feet Installed						At Weekly level production over under
2
3	Item	ACTIVITY	Size	LF /Qty	Man Hours	Wk- 1	Wk-2	Wk-3	Wk-4	Wk-5	Wk-6	12-Nov
4	1	Piping Mechanical Eq Room	10"	20	20	1	1	1	1	1	1
5			8"	280	462	40						-40
6			5"	280	477	60						80
7			5"	280	477	30	110					140
Sheet3

Cell Formulas
Range	Formula
L5	=SUM($F5:OFFSET($F5,,COUNT($F5:$K5)-1))/COUNT($F5:$K5)*6-$D$5
L6	=SUM($F6:OFFSET($F6,,COUNT($F6:$K6)-1))/COUNT($F6:$K6)*6-$D$5
L7	=SUM($F7:OFFSET($F7,,COUNT($F7:$K7)-1))/COUNT($F7:$K7)*6-$D$5

For your third condition:

Wk 1 = 30
Wk 2 = 110

The way I have worked around it is, I have derived an average from the sum of these 2 weeks work done so 30 + 110 divided by 2 = 70.

I have then multiplied 70*6 which is 420 in total which when subtracts 280 gives you 140.

Is this something your looking for?

Remember, this assumes your new average based on the amount of work done in the number of weeks.

So in case you had 3 weeks where:

Wk1 = 30
Wk2 = 110
Wk3 = 30

Then your new average would be = 30 + 110 + 30 = 170 / 3 about 56.666667. This is then multiplied into 6 to give 340 which will subtract 280 to return an over of 60.