How to Find the Average Again

How to calculate average age by year/month/appointment in Excel?

For example, you have a student roster with birthdays and ages in Excel, how could y'all calculate the boilerplate age by a given month or twelvemonth? And what if calculating the boilerplate by a specific engagement in Excel? This article will introduce several tricks to solve them in Excel easily.

  • Calculate boilerplate by year/month with array formulas in Excel
  • Calculate average past year/month with Filter control in Excel
  • Calculate average by date in Excel
  • Batch calculate boilerplate ages past year or month in Excel

Calculate average past year/calendar month with array formulas in Excel

For example y'all take a table equally post-obit screen shot shown. You lot can calculate the average age by twelvemonth or month with array formulas quickly in Excel.

Boilerplate historic period past Month : Select a blank cell besides the tabular array, for example Prison cell F2, enter the formula =SUM((Calendar month(B2:B15)=12)*C2:C15)/SUM(IF(MONTH(B2:B15)=12,1)) into it, and press the Ctrl + Shift + Enter keys at the aforementioned fourth dimension.

Notation: In this formula, the B2:B15 is the Birthday column, C2:C15 is the Age column, 12 is the specified calendar month "December" which yous will boilerplate past, and you can change them based on your needs.

Average age past Year : Select a blank cell besides the table, says Cell F4, enter the formula =SUM((YEAR(B2:B15)=1990)*C2:C15)/SUM(IF(YEAR(B2:B15)=1990,1)) into it, and printing the Ctrl + Shift + Enter keys at the same time.

Annotation: In this formula, the B2:B15 is the Birthday column, C2:C15 is the Age cavalcade, 1990 is the specific year you want to boilerplate past, and yous can change them based on your needs.

note ribbon Formula is as well complicated to call back? Salve the formula equally an Auto Text entry for reusing with only one click in future!
Read more…     Costless trial

Calculate average by year/month with Filter command in Excel

In our example table equally following screen shot shown, we can likewise filter the birthday column firstly, and so calculate average by year or month after filtering with Subtotal role in Excel.

1. Select the Birthday Column and click the Data > Filter.

two. Click  button as well cavalcade header of Altogether cavalcade, and and then in the driblet down listing click the Date Filters > All Dates in the Period > September (or whatsoever other month you want to boilerplate by). See screen shot below:

Notation: If you want to average historic period past specific year, you demand to click the doc-average-by-month-5 button besides column header of Birthday column, and then uncheck all years except the year you will boilerplate by, and click the OK push. See screen shot below:

three. At present the ages are filtered by the specific yr or month. Select any blank cell, says Cell C42, the formula =SUBTOTAL(one,C3:C9) (C3:C9 means all filtered out ages in the Historic period column), and press the Enter primal.


Calculate average by date in Excel

Sometimes, you may exist required to calculate the average with given engagement. For example, you have a table as following screen shown, and you need to calculate the average of Amount occurred on 2014-04-04, you tin can solve it every bit following:

Select a blank cell, enter the formula =AVERAGEIF(J2:J24,P2,M2:M24) into it, and press the Enter key. Then yous will get the average of the specified engagement.

Note: In formula =AVERAGEIF(J2:J24,P2,M2:M24), the J2:J24 is the engagement column, P2 is the reference cell with date you lot volition average by,   M2:M24 is the amount cavalcade where you lot will calculate boilerplate, and y'all can alter them based on your needs.


Batch calculate average ages past yr or month in Excel

If y'all need to calculate boilerplate age of each month (from January to Dec) or each year in Excel, apply the complicate array formula may exist a litter fourth dimension-consuming. Here I will innovate the 3rd-part add-in of Kutools for Excel to solve it.

Kutools for Excel - Includes more than 300 handy tools for Excel. Full characteristic free trial 30-twenty-four hours, no credit card required! Become It At present

1. Select the engagement cavalcade that you will calculate boilerplate by, and click the Kutools > Format > Apply Appointment Formatting.

two. In the opening Apply Appointment Formatting dialog box, select a date formatting showing simply month name in the Date formatting box, such as "Mar" or "March", and click the Ok push. Come across screenshot:

Tip: If yous need to calculate average ages past year, delight select a date formatting only showing year (such equally "2001") in the Date formatting box.

Use Appointment Formatting is i of Kutools for Excel utilities, and it can assist you modify mass dates to specific date formatting meeting different customers and different countries in Excel.

Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now

3. Proceed selecting the date cavalcade, and click the Kutools > To Actual. This utility will convert the date to the text of calendar month name.

To Actual is i of Kutools for Excel utilities, and it tin can aid you supplant the actual value (showing in formula bar, such as a formula =A1 + B1) with the displaying value (which y'all encounter in the selected cell, such as formula result 10). Total Feature Free Trial thirty-day!

4. Select the range that you will average by month/yr, and click the Kutools > Content > Advanced Combine Rows.

doc average by month 12

Advanced Combine Rows is one of Kutools for Excel utilities, and it can help you combine / merge the rows based on the aforementioned value in one column, and and so practise some calculations for other columns, such as sum, count, average, max, etc. Full Feature Free Trial 30-24-hour interval!

5. In the opening Combine Rows Based on Column dialog box, specify the settings as above screenshot shown:

  1. Click the Name column, so click the Combine > Comma;
  2. Click the Birthday column, and then click the Main Key.
  3. Click the Age column, and and then click the Calculate > Average.

6. Click the Ok push button. So you will come across the average age of each month is calculated with listing corresponding names. Run into below screenshot:

Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit bill of fare required! Go Information technology At present


Demo: calculate average age by yr/month/date in Excel

Kutools for Excel includes more than 300 handy tools for Excel, gratis to try without limitation in 30 days. Download and Free Trial Now!


Related manufactures:


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert circuitous formulas, charts  and anything that yous accept used earlier; Encrypt Cells with password; Create Mailing Listing and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (hands read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Indistinguishable Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Discover and Fuzzy Observe in Many Workbooks; Random Select...
  • Verbal Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Cheque Boxes and more than...
  • Extract Text, Add Text, Remove past Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by calendar month/week/day, frequency and more than; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Carve up Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Part/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or arrangement. Full features 30-day free trial. threescore-day money back guarantee.

kte tab 201905


Office Tab Brings Tabbed interface to Role, and Brand Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint , Publisher, Admission, Visio and Projection.
  • Open and create multiple documents in new tabs of the aforementioned window, rather than in new windows.
  • Increases your productivity by l%, and reduces hundreds of mouse clicks for you lot every 24-hour interval!

officetab bottom

Comments (ane)

No ratings all the same. Exist the first to charge per unit!

gainesdiany1939.blogspot.com

Source: https://www.extendoffice.com/documents/excel/2594-excel-average-age-by-year-date-month.html

Belum ada Komentar untuk "How to Find the Average Again"

Posting Komentar

Iklan Atas Artikel

Iklan Tengah Artikel 1

Iklan Tengah Artikel 2

Iklan Bawah Artikel