Averaging last cells in range ignoring certain cells

Crizznitch

Board Regular
Joined
Nov 18, 2004
Messages
120
Howdy, I am inputting monthly data and need to take the average of the last 12 months that have been entered (over a two-year time period max and the first year is already completely entered). However, at the end of the first year there are two rows that sum and average the data from year 1, and then a blank row.

I was wondering if there is a formula, or a way to piece together a formula, that will take the last twelve months as I am entering the data from the months this year and ignore the SUM and AVG rows. I currently have a formula that ignores the blank row, but not the other two rows.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,021
Office Version
  1. 365
Is your data arranged in columns or rows? Better yet, can you post a sample of your data? (see download Colo's HTML maker at the bottom of this page)
 

Crizznitch

Board Regular
Joined
Nov 18, 2004
Messages
120
Is your data arranged in columns or rows?

Its arranged in columns. I can't download the program where I work to paste it, but it's set up like this:

_____A______B___
1 Month____Sales
2 JAN______500
3 FEB______400
4 MAR_____400
5 APR______650
6 MAY______500
7 JUN______450
8 JUL______600
9 AUG_____550
10 SEP_____500
11 OCT_____400
12 NOV_____550
13 DEC_____450
14 Total____5950
15 Avg______496
16
17 JAN______500
18 FEB______400
19 MAR_____....
...(and so on)...
28 DEC_____....
29 Total_____900
30 Avg______450
31
32 Avg Last 12 Months:

If this doesn't help, I can paste it later at home.
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,825
Office Version
  1. 365
Platform
  1. Windows
Try the following...

=AVERAGE(SUBTOTAL(9,OFFSET(B2,LARGE(IF((A2:A30<>"Total")*(A2:A30<>"Avg")*(B2:B30<>""),ROW(A2:A30)-CELL("row",A2)),ROW(INDIRECT("1:12"))),0)))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Hi,

Try:

=AVERAGE(INDEX(B1:B28,LARGE(((IF((B2:B28<>"")*(A2:A28<>"Total")*(A2:A28<>"Avg"),ROW(A2:A28)))),12)):B13,B17:INDEX(B1:B28,MAX((IF((B2:B28<>"")*(A2:A28<>"Total")*(A2:A28<>"Avg"),ROW(A2:A28))))))

Confirmed with Ctrl + shift + enter in B32.

This will not give correct result until first number for year 2 is entered.
 

Forum statistics

Threads
1,147,623
Messages
5,742,208
Members
423,712
Latest member
edzubur

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top