Average Age using decimals

tcarwardine

New Member
Joined
Jul 13, 2016
Messages
22
Office Version
365, 2016
Platform
Windows
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?
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,719
Office Version
365
Platform
Windows
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?
 
Last edited:

tcarwardine

New Member
Joined
Jul 13, 2016
Messages
22
Office Version
365, 2016
Platform
Windows
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.
 

tcarwardine

New Member
Joined
Jul 13, 2016
Messages
22
Office Version
365, 2016
Platform
Windows
Ok, try this formula. I've given 2 examples.

Average Age

ABC
1
210.04 11.015
311.11
4
5
610.04 12.032
711.00
815.10
912.03
1011.11

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:56px;"><col style="width:18px;"><col style="width:65px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,719
Office Version
365
Platform
Windows
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. :cool:
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,408
Office Version
365
Platform
Windows
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.

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">10.04</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #FFFF00;;">11.015</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">11.11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">10.04</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #FFFF00;;">12.032</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">15.1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">12.03</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">11.11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:15.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">SQL orig inv & cost</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=DOLLARFR(<font color="Blue">AVERAGE(<font color="Red">DOLLARDE(<font color="Green">CHOOSE(<font color="Purple">{1},A2:A3</font>),12</font>)</font>),12</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C6</th><td style="text-align:left">=DOLLARFR(<font color="Blue">AVERAGE(<font color="Red">DOLLARDE(<font color="Green">CHOOSE(<font color="Purple">{1},A6:A10</font>),12</font>)</font>),12</font>)</td></tr></tbody></table></td></tr></table><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,099,684
Messages
5,470,113
Members
406,681
Latest member
sachinmasurkar

This Week's Hot Topics

Top