How do I take a percentage of total time over a 7 day period when time exceeds 24:00 hrs?

[seanpod]

I have attached a screenshot of what I am working with and attempting to accomplish.

For 'Tank Usage over 7 Days' I have calculated average time & average minutes wherein average time is by function =AVERAGE(A1, B1, etc.) in the format [hh]:mm and average minutes is by function =(A1)*24*60 in the format General.

Then I took totals for all tanks using the corresponding cells, but what is the formula to turn this data into percentages by average % of each tank, and by total % of each tank over a 7 day period when time exceeds 24 hours?

Thanks.

[6StringJazzer]

You can't average percentages. You calculate an average percentage by using the totals of the two numbers used to calculate the percentage. Which leads my next question: What does % mean in this case? There is no context.

This would be a lot easier to help with if you attach your Excel file instead of an image.

[seanpod]

You can't average percentages. You calculate an average percentage by using the totals of the two numbers used to calculate the percentage. Which leads my next question: What does % mean in this case? There is no context.

This would be a lot easier to help with if you attach your Excel file instead of an image.

Why can't you average percentages?

If you treat them like ordinary values then this formula seems to work: =IF(COUNT(AL9:AL201),AVERAGE(AL9:AL201),"")

[ChemistB]

Again, hard to determine without knowing what we are discussing but take for example,

Group 1: 99, 100 Avg = 99.5 %(out of 100) = 99.5
Group 2: 88, 85, 78, 83, 87, 89 Avg = 85, % = 85.0
Avg % = 92.3 which is decptively high if we are measuring the same thing in Group 1 and Group 2
Real Avg % should be 88.6

[Jonmo1]

You can't average percentages.

Not necessarily true..

Why can't you average percentages?

More context is needed.
It really depends on what the percentage is, or how it's calculated.

If the devisor is the same for each percentage, then yes, the average will be correct.
But if you have different devisors among each percentage, then the average will be inaccurate (as Chemist demonstrated in post #4)


But I think your issue probably has nothing to do with that.
You're probably having an issue with displaying time greater than 24 hours.
Try formatting your cell as [hh]:mm:ss to show time past 24 hours.

[seanpod]

Not necessarily true..


More context is needed.
It really depends on what the percentage is, or how it's calculated.

If the devisor is the same for each percentage, then yes, the average will be correct.
But if you have different devisors among each percentage, then the average will be inaccurate (as Chemist demonstrated in post #4)


But I think your issue probably has nothing to do with that.
You're probably having an issue with displaying time greater than 24 hours.
Try formatting your cell as [hh]:mm:ss to show time past 24 hours.

I would post the spreadsheet but it is for work and I am at home now, so I will try to explain more below.

So across a period of time (e.g., one week), I have summed the total time ([hh]:mm) in cell A1 (just for example) for each tank while in a production phase, then in cell A2 converted that time into minutes (by multiplying the total time from cell A1 by 24 and then again by 60), after which I used the function '=(A2/1440)*100' in cell A3 to get my percentages.

Therefore, for seven days I have seven different percentages, all with the same divisor of 1440, which is the amount of minutes in a day (24*60). Now of course when I apply the 'IF(COUNT(xx.....' formula I posted in my last post, I am taking the values derived as percentages (seven total) across the week, so the divisor rule is no longer in effect since the numbers have already been divided.

So I am correct in what I did, right?

[6StringJazzer]

This fills in a lot of missing information. So the percentage is the percent utilization of a tank during a 24-hour period--the percent of the day that it is used. And you want the average percent utilization over 7 days. So, yes in this case it is valid to average the percentages, because the divisors are all the same. (The explanation immediately above doesn't match your image from the first post so it's a little difficult to put this all together.)

I would probably use this formula, although it effectively does the same thing as yours

Please Login or Register  to view this content.