Time Value Distribution Automated?

[Callum456]

Hi all, I am new to this so bare with me. Below is the current solution to my predicament, done manually. I would like to automate the distribution column. For this, the numerator is the TVU column, and the denominator is determined via the COUNTIF() function. The issue is that I cannot get excel to reference the correct TVU, it is often referencing a 0 and trying to divide by the COUNTIF(). The number I need to reference is the value that is followed by a 0. (ex. 8:25 am occurs 3 times, so i need to divide the difference between 8:17 and 8:25 by 3. to get a time for each column) If any one has any insight on fixing this reference problem please share.

thank you, Callum

Time	TVU =(time2-time1)	Distribution (?)	CountIF(=COUNTIF($A$2:$A$6,A2))
8:00 am	-	-	1
8:17 am	17:00	17:00/1	1
8:25 am	8:00	8:00/3	3
8:25 am	0	8:00/3	3
8:25 am	0	8:00/3	3
8:40 am	15:00	15:00/1	1

 

[Rijnsent]

Hi Callum,
welcome to the forum! Something like this might do the trick.
Cheers,
Koen

Map1
	A	B	C	D
1	Time	TVU =(time2-time1)	AverageTime	SplitTime
2	8:00 AM	00:00	00:00:00	08:00:00
3	8:17 AM	00:17	00:17:00	08:17:00
4	8:25 AM	00:08	00:02:40	08:25:00
5	8:25 AM	00:00	00:02:40	08:27:40
6	8:25 AM	00:00	00:02:40	08:30:20
7	8:40 AM	00:15	00:15:00	08:40:00
Blad1

Cell Formulas
Range	Formula
B2:B7	B2	=IFERROR(A2-A1,0)
C2:C7	C2	=AVERAGEIF($A$2:$A$7,A2,$B$2:B5)
D2:D7	D2	=A2+SUMIF(A$1:A1,A2,C$1:C1)

 

[Callum456]

Hi thanks for helping out.

When I use the Avg if i get a DIV/0 error. How did you avoid this?

-Callum

 

[Rijnsent]

Could you post your data/formulas with XL2BB? (See the link in the box where you make your post.)
Thanks