Calculating the average when not using whole numbers

[foxystoat82]

I'm tracking the results of my sports team's beep test over time. Calculating average distance and average time is fine, I can't work out how to find the average of the Beep Test Levels.

The Levels in the Beep Test go from 1 to 20 in increments of 13 decimal places, i.e. going from Level 7 to 8 does not go 7.7 7.8 7.9 8.0 8.1, it goes 7.9 7.10 7.11 7.12 7.13 8.0 8.1.

For example, as shown in the image, I would expect the average Level for Player 2 to be 8.0 (7.07 in month 1, and 8.07 in month 2, but Excel is assuming the Levels are whole numbers it is averaging at 7.57.

Can I change the average function to know I'm not working in whole numbers and calculate on the .13, or should I change the way the data itself is input?

Hope this makes sense! Thanks

 

[SanjayGMusafir]

Can't access things from screenshots. Share XL2BB to understand the whole scenario and think of a solution.

 

[ebea]

There are two things in what you write, which confuse, both yourself, and others, and it is that you give an example of three numbers, and do an average of these.
And then, you write that it is from 1 to 20, with 13 decimals places.

If you, as you wrote in your example has used all numbers, you would have got the number 8.0........

Try with the formula (and all numbers): =AVERAGE(ROUND(ROUND(A1:A20;13));13)

 

[RoryA]

7.07 actually means 7 and 7/14, not 7 and 7/100 so you can't do straight maths on it. You need to convert it to true decimals, take the average, and then convert that back to 14ths.