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]

Thanx Fairwinds


Problem solved as I desired


Regards

Mian Mazher

 

[Aladin Akyurek]

...Problem solved as I desired...

That sounds great.

Here is another attempt after you specified how the average must be computed...

WeightedAvg 2.xls
	A	B	C	D	E	F
1	Case2	Start	3	End	5
2		Actual Time	Consumption	Converted Time	Target TimeDiffs	Avg
3		10:00	45	10.00	20.42	47.18756229
4		15:25	46.2	15.42	19.58
5	Start	20:25	47	20.42	10.17
6		40:00	48	40.00
7	End	50:10	46	50.17
8		60:00	45.25	60.00
9
Sheet1

Formulas...

C1:

=MATCH(B1,$A$3:$A$8,0)

E1:

=MATCH(D1,$A$3:$A$8,0)

E3:

=IF(ROW()-ROW($E$3)+1<=$E$1-$C$1+1,INDEX($D$3:$D$8,ROW()-ROW($E$3)+$C$1),"")

E4, which must be copied all the way down:

=IF(ROW()-ROW($E$3)+1<=$E$1-$C$1+1,INDEX($D$3:$D$8,ROW()-ROW($E$3)+$C$1)-INDEX($D$3:$D$8,ROW()-ROW($E$3)+$C$1-1),"")

F3:

=SUMPRODUCT(E3:INDEX(E3:E8,COUNT(E3:E8)),INDEX(C3:C8,C1):INDEX(C3:C8,E1))/VLOOKUP(D1,A3:D8,4,0)

 

[mianmazher]

Thanks

Sir Aladin


Regards

Mian Mazher