Average Formula Time Based Calculation

mianmazher

Active Member
Joined
Jan 24, 2004
Messages
412
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
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

mianmazher

Active Member
Joined
Jan 24, 2004
Messages
412
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

Active Member
Joined
Jan 24, 2004
Messages
412
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
Joined
Jan 24, 2004
Messages
412

ADVERTISEMENT

Hi Sir Aladin


Still awaiting help regarding this matter


Regards



Mian Mazher
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638

ADVERTISEMENT

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

mianmazher

Active Member
Joined
Jan 24, 2004
Messages
412
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
Joined
May 15, 2003
Messages
8,638
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,804
Messages
5,598,158
Members
414,214
Latest member
marketingnumbersguy

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top