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

 

[Aladin Akyurek]

=AVERAGE(INDEX(C:C,MATCH("Start",A:A,0)):INDEX(C:C,MATCH("End",A:A,0)))

 

[mianmazher]

Sir Aladin

I want to Take into account column B in which the time entered as text format which represent minute and seconds.

Regards

Mian Mazher

 

[mianmazher]

Book1
	A	B	C	D
1		Time as text	Consumption	Time as numbers
2	Start	10:00	45	10
3		15:25	46.2	5.42
4		20:25	47	5
5		40:00	48	19.58
6		50:10	46	10.17
7	End	60:00	45.25	9.83
8
9				60
10
11	Sumproduct	2787.8715
12
13	Average	46.464525
Sheet1

this is what I have done


Regards

Mian Mazher

 

[mianmazher]

Hi Sir Aladin


Still awaiting help regarding this matter


Regards



Mian Mazher

 

[mianmazher]

Hia all

Waiting for help

Regards

Mian Mazher

 

[fairwinds]

So you want average of those times or...?

 

[mianmazher]

Hi fairwinds

I want to average the consumption based on the times

I have the contnious process in which the chemicals are poured continuously

to maintain the quailty we have to change the quantity of chemicals

suppose when I started the production the qty was 60 for ten minutes
Start 10 60
15 55
25 57
End 30 60

the average chemical used will be calculated as follows
(60*10)+(55*5)+(57*10)+(60*5)/30
1745/30
58.166

If I move the start to

10 60
Start 15 55
25 57
End 30 60
then the average will be

(55*15+57*10+60*5)/30

1695/30
56.5

if i move the end to

start 10 60
15 55
End 25 57
30 60

then the average will be computed as follows

(60*10+55*5+57*10)/25

1445/25
57.8

the time is in the text format and its in minutes and seconds

as 10:20, 15:50 25:30 and so on


Its think its clear what I am saying


Regards

Mian Mazher

 

[fairwinds]

Does it look ok?

I used time as number as I did not understand how your times as text were to be translated.

Book4
	A	B	C	D
1		Time	Consumption	Timeasnumber
2	Start	10:00	45	10
3		15:25	46.2	5.42
4		20:25	47	5
5		40:00:00	48	19.58
6		50:10:00	46	10.17
7	End	60:00:00	45.25	9.83
8
9
10
11	Average	46.2998192
Sheet5

 

[mianmazher]

Hi MR Excel Forum Members

Still facing Problems

Book2
	A	B	C	D	E	F	G	H	I
1	Case1
2		Actual Time	Consumption	Time(Numbers)		I want to sum these Calculations in Cell B11
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		50:10	46	50.17		46	10.17
8	End	60:00	45.25	60.00		45.25	9.83
9
10						Average	46.87014
11	Average	46.87014
12
Sheet1