Average Age using decimals

[tcarwardine]

Hi

I have been given a sheet of ages and asked to calculate the average. However the ages are in decimals e.g. 10.04 = 10 years and 4 months, 11.11 = 11 years and 11 months.

Any ideas how to calculate an accurate average in excel?

 

[Peter_SSs]

For your example, the average is 11 years and 1.5 months. How would you want to display that result?

Also,
- For the original data how is an exact number of years shown? Is it 11.00?
- Is the data stored as text values or are they actual numbers, perhaps formatted to show 2 decimal places?

 

[tcarwardine]

For your example, the average is 11 years and 1.5 months. How would you want to display that result?

Also,
- For the original data how is an exact number of years shown? Is it 11.00?
- Is the data stored as text values or are they actual numbers, perhaps formatted to show 2 decimal places?

I would like to display it as 11.015

They are currently numbers to 2dp but can change that if required.

 

[Peter_SSs]

I would like to display it as 11.015

They are currently numbers to 2dp but can change that if required.

Ok, try this formula. I've given 2 examples.

Excel Workbook
	A	B	C
1
2	10.04		11.015
3	11.11
4
5
6	10.04		12.032
7	11.00
8	15.10
9	12.03
10	11.11
Average Age

 

[tcarwardine]

Ok, try this formula. I've given 2 examples.

Average Age

	A	B	C

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:56px;"><col style="width:18px;"><col style="width:65px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: right"]10.04[/TD]

[TD="align: right"]11.015[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: right"]11.11[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="align: right"]10.04[/TD]

[TD="align: right"]12.032[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="align: right"]11.00[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="align: right"]15.10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]
[TD="align: right"]12.03[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]
[TD="align: right"]11.11[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula C2 =INT(SUMPRODUCT(INT(A2:A3)+(A2:A3-INT(A2:A3))/12*100)/COUNT(A2:A3))+(SUMPRODUCT(INT(A2:A3)+(A2:A3-INT(A2:A3))/12*100)/COUNT(A2:A3)-INT(SUMPRODUCT(INT(A2:A3)+(A2:A3-INT(A2:A3))/12*100)/COUNT(A2:A3)))*12/100 C6 =INT(SUMPRODUCT(INT(A6:A10)+(A6:A10-INT(A6:A10))/12*100)/COUNT(A6:A10))+(SUMPRODUCT(INT(A6:A10)+(A6:A10-INT(A6:A10))/12*100)/COUNT(A6:A10)-INT(SUMPRODUCT(INT(A6:A10)+(A6:A10-INT(A6:A10))/12*100)/COUNT(A6:A10)))*12/100 <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Amazing, thank you so much

 

[Peter_SSs]

Amazing, thank you so much

You're welcome.
If you haven't done so already you should check a reasonable number of these manually as I'm not that confident of the formula.

 

[FormR]

Hi, just another option you can try, word of warning though, as written this will treat blank cells as zero's when doing the averaging.

Excel 2013/2016
	A	B	C
1
2	10.04		11.015
3	11.11
4
5
6	10.04		12.032
7	11
8	15.1
9	12.03
10	11.11
SQL orig inv & cost

Cell Formulas
Range	Formula
C2	=DOLLARFR(AVERAGE(DOLLARDE(CHOOSE({1},A2:A3),12)),12)
C6	=DOLLARFR(AVERAGE(DOLLARDE(CHOOSE({1},A6:A10),12)),12)

 

[Peter_SSs]

Hi, just another option

ray:

I have never mastered those function at all.