Average Formula Time Based Calculation

[mianmazher]

Book2
	A	B	C	D
1		Time	Consumption
2	Start	10:00	45
3		15:25	46.2
4		20:25	47
5		40:00:00	48
6		50:10:00	46
7	End	60:00:00	45.25
8
9	Average	46.464525
Sheet1

In Column B I have the time values in the text format which represent minutes and seconds

in Column A I have to write the start and the Stop as shown

If start and stop are in the position as shown above then the average should be 46.464525

If the start is entered in A3 and End in A7 then the Average should be

46.664525

in the same way if the end is shifted up or down the average changes automatically


Hope that u people understand what I trying to say


Waiting for someone to help


Regards

Mian Mazher

 

[mianmazher]

Book2
	A	B	C	D	E	F	G	H
1	Case 2
2		Actual Time	Consumption	Time(Numbers)
3		10:00	45	10.00
4		15:25	46.2	15.42
5	Start	20:25	47	20.42		47	20.42
6		40:00	48	40.00		48	19.58
7	End	50:10	46	50.17		46	10.17
8		60:00	45.25	60.00
9						Average	47.18771
10	Average	47.18771
11
12
Sheet1

I want like this when the position of starta nd stops change the Average should also change

Regards

Mian Mazher

 

[fairwinds]

Just a typo, got the columns wrong, this works if you apply it to the layout in my last post.


=SUMPRODUCT(INDEX(C1:C10,MATCH("Start",A1:A10,0)):INDEX(C1:C10,MATCH("End",A1:A10,0))*INDEX(D1:D10,MATCH("Start",A1:A10,0)):INDEX(D1:D10,MATCH("End",A1:A10,0)))/SUM(INDEX(D1:D10,MATCH("Start",A1:A10,0)):INDEX(D1:D10,MATCH("End",A1:A10,0)))

 

[SIXTH SENSE]

Hi!
I know that this is possible with formula.
But lets try VBA for ease.

Book1
	A	B	C	D	E	F	G
1	Case2
2		ActualTime	Consumption	Time(Numbers)
3		0:10:00	45	10.00
4		0:15:25	46.2	15.42
5	Start	0:20:25	47	20.42		47	20.42
6		0:40:00	48	40.00		48	19.58
7	End	0:50:10	46	50.17		46	10.17
8		1:00:00	45.25	60.00
9						Average	47.18771
10	Average	47.1877076
11
12		InputtoUDF
Sheet3

The code!<font face=Courier New><SPAN style="color:#00007F">Function</SPAN> AVERTIME(TABLE<SPAN style="color:#00007F">As</SPAN> Range)<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Double</SPAN><SPAN style="color:#00007F">Dim</SPAN> P()<SPAN style="color:#00007F">Dim</SPAN> ST()<SPAN style="color:#00007F">Dim</SPAN> COMP()<SPAN style="color:#00007F">ReDim</SPAN> P(TABLE.Rows.Count)<SPAN style="color:#00007F">ReDim</SPAN> ST(TABLE.Rows.Count)<SPAN style="color:#00007F">ReDim</SPAN> COMP(TABLE.Rows.Count)<SPAN style="color:#00007F">For</SPAN> I = 1<SPAN style="color:#00007F">To</SPAN> TABLE.Rows.Count
    P(I) = TABLE(I, 1)
    ST(I) = TABLE(I, 2) * 24 * 60
    COMP(I) = TABLE(I, 3)<SPAN style="color:#00007F">Next</SPAN> I<SPAN style="color:#00007F">For</SPAN> I =<SPAN style="color:#00007F">LBound</SPAN>(P)<SPAN style="color:#00007F">To</SPAN><SPAN style="color:#00007F">UBound</SPAN>(P)
    <SPAN style="color:#00007F">If</SPAN> UCase(P(I)) = "START"<SPAN style="color:#00007F">Then</SPAN>
    STARTX = I
    <SPAN style="color:#00007F">ElseIf</SPAN> UCase(P(I)) = "END"<SPAN style="color:#00007F">Then</SPAN>
    ENDX = I
    <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN><SPAN style="color:#00007F">Next</SPAN> I
PREVTIME = 0
SUMP = 0
SUMX = 0<SPAN style="color:#00007F">For</SPAN> I = STARTX<SPAN style="color:#00007F">To</SPAN> ENDX
    SUMP = SUMP + (ST(I) - PREVTIME) * COMP(I)
    SUMX = SUMX + (ST(I) - PREVTIME)
    PREVTIME = ST(I)<SPAN style="color:#00007F">Next</SPAN> I
AVERTIME = SUMP / SUMX<SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Function</SPAN></FONT>

 

[Aladin Akyurek]

Just a typo, got the columns wrong, this works if you apply it to the layout in my last post.


=SUMPRODUCT(INDEX(C1:C10,MATCH("Start",A1:A10,0)):INDEX(C1:C10,MATCH("End",A1:A10,0))*INDEX(D1:D10,MATCH("Start",A1:A10,0)):INDEX(D1:D10,MATCH("End",A1:A10,0)))/SUM(INDEX(D1:D10,MATCH("Start",A1:A10,0)):INDEX(D1:D10,MATCH("End",A1:A10,0)))

A weighted average... It seems waters are cleared up.

All this can be a bit shorter:

WeightedAvg.xls
	A	B	C	D	E	F
1	Case2				Start	3
2		ActualTime	Consumption		End	4
3		10:00	45	10.00
4		15:25	46.2	15.42	47.66207
5	Start	20:25	47	20.42
6		40:00	48	40.00
7	End	50:10	46	50.17
8		60:00	45.25	60.00
Sheet1

D3:

=(IF(LEN(B3)<6,"0:"&B3,B3))*1440

F1, copied to F2...

=MATCH(E1,A3:A8,0)

E4 (computes the weighted average)...

=SUMPRODUCT((INDEX(D3:D8,F1):INDEX(D3:D8,F2))*(INDEX(C3:C8,F1):INDEX(C3:C8,F2)))/SUM((INDEX(D3:D8,F1):INDEX(D3:D8,F2)))

 

[mianmazher]

Sir Aladin


Really not seen a one like u (I think the most genius Of all in this Forum)

all have provided a solution but yours solution is the easiest)


Thanks


REgards


Mian Mazher

 

[mianmazher]

Hi Sir Aladin

Again Facing the problem.


The average should be 47.18771 instead of 47.662069 as computed below

The start time is 20.42

The end time is 50.17

(20.42*47)+((40.00-20.42)*48)+((50.17-40)*46)/50.17


I Hope this Helps to solve my problem


Regards

 

[fairwinds]

My guess as I cannot view the formulas:

The formula in F2 should show 5
Change the formula in F1 to =MATCH(E1,$A$3:$A$8,0) before dragging it to F2.

 

[mianmazher]

hi fairwinds

Problem not solved


Average comming 46.9080633

instead of comming 47.18771


Plz help whats wrong and where


Regards

Mian Mazher

 

[mianmazher]

Hi All

Waiting For help


Regards

Mian Mazher

 

[fairwinds]

Ok,

Apart from the correction I mentioned before;

Here I inserted one column and changed the SUMPRODUCT formula.

Actually I really dont understand this average calculation, but anyway:

In E3 and dragg down: =IF(ROW()-2=$G$1,D3,D3-D2)

In F4: =SUMPRODUCT((INDEX(E3:E8,G1):INDEX(E3:E8,G2))*(INDEX(C3:C8,G1):INDEX(C3:C8,G2)))/INDEX(D3:D8,G2)

Book2
	A	B	C	D	E	F	G
1	Case2					Start	3
2		ActualTime	Consumption			End	5
3		10:00	45	10.00	10.00
4		15:25	46.2	15.42	5.42	47.18770764
5	Start	20:25	47	20.42	20.42
6		40:00	48	40.00	19.58
7	End	50:10	46	50.17	10.17
8		60:00	45.25	60.00	9.83
9
Sheet5