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

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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)
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,293
Members
448,564
Latest member
ED38

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
Back
Top