# Averaging last cells in range ignoring certain cells

#### Crizznitch

##### Board Regular
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

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### Oaktree

##### MrExcel MVP
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
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
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
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.

#### Crizznitch

##### Board Regular
Thank you Domenic and fairwinds! Both formulas worked great.

Replies
14
Views
269
Replies
3
Views
1K
Replies
4
Views
253
Replies
0
Views
56
Replies
3
Views
324

1,181,614
Messages
5,930,942
Members
436,767
Latest member
Langaws

### 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?

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