Forum menu
How can i work this out? What's the formula?
Group A has spent on average, 12mins 45secs doing something. There are 200 people in Group A.
Group B has spent on average, 11mins 58 secs doing the same thing. There are 58 people in Group B.
I only have the average time values - not the individual data.
How can i work out the average time Group A and Group B have been doing the thing, knowing the values will change over time?
Sum of times / sum of people
So (NumPeople1 * MeanTime1 + NumPeople2 * MeanTime2) / (NumPeople1 + NumPeople2)
Somebody will probably correct my stats but (group a time * count of group a) + (group b time * count of group b) /count a + count b? With more brackets
SRIOUSLY?
Not sure why it needs Excel, but I don't understand what you mean by "knowing that the values will change over time"?
(12.75 x 200 + 11.66667 x 58)/258 = 12.5064 or 12min 30.4s. If you use Excel it's best to convert min/sec into min before doing any multiplication, so 12m 45s = 12 + 45/60 = 12.75min, similarly at the end if you want min and sec it's (12.5064 - 12) x 60 = 30.4s
hang on. I do
?!
I thought it was 200 and 58?
losing my mind. Long day.
it's the getting the timing value right in the formula I couldn't get my head around, converting the timer to a usable number. And then making it into a replicatable formula.
Edited to make me look silly Pook! Cheeky.
nope, caught out by the 15 minute or whatever lag for edits kicking in.
No cheekiness intended.
Is your problem now that you have text value in cells you need to do maths with?
no, it's that I have a 'time' value which is a clock time i.e. it's 8.27pm, rather than a timer time of 8m27s.
Try a custom format, mm:ss.
If you know where the decimal place is, the you can just split the string using left, mid and right etc e.g.
=LEFT(B5,1)*60+MID(B5,3,2)
will convert 8.27 into 507 seconds
If you want a dynamic version use:
=LEFT(B5,FIND(".",B5)-1)*60+MID(B5,FIND(".",B5)+1,2)
This will locate the decimal place and split the time accordingly.
I was overthinking it, Excel will just do the arithmetic with the time values and you just have to format the result as a time. As kelron says, format the times as mm:ss (you may need to type in the hours as 00: when inputting) and then just multiply each time by the group size, add them and divide by the total number. Then format the answer cell the same as the original times.
^ that's it, cheers greybeard!