# Average Formula Time Based Calculation

#### mianmazher

##### Active Member
Book2
ABCD
1TimeConsumption
2Start10:0045
315:2546.2
420:2547
540:00:0048
650:10:0046
7End60:00:0045.25
8
9Average46.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

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

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

#### mianmazher

##### Active Member

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

##### Active Member
Book1
ABCD
1Time as textConsumptionTime as numbers
2Start10:004510
315:2546.25.42
420:25475
540:004819.58
650:104610.17
7End60:0045.259.83
8
960
10
11Sumproduct2787.8715
12
13Average46.464525
Sheet1

this is what I have done

Regards

Mian Mazher

#### mianmazher

##### Active Member

Still awaiting help regarding this matter

Regards

Mian Mazher

Hia all

Waiting for help

Regards

Mian Mazher

#### fairwinds

##### MrExcel MVP

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

#### mianmazher

##### Active Member
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

##### MrExcel MVP
Does it look ok?

I used time as number as I did not understand how your times as text were to be translated.
Book4
ABCD
1TimeConsumptionTimeasnumber
2Start10:004510
315:2546.25.42
420:25475
540:00:004819.58
650:10:004610.17
7End60:00:0045.259.83
8
9
10
11Average46.2998192
Sheet5

#### mianmazher

##### Active Member

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,168,082
Messages
5,857,251
Members
431,866
Latest member
Paula777

### 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.

### Which adblocker are you using?

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

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