# Average LAST 6 values (Excluding ones with X in column A

Hi, at the moment I use... =AVERAGE(OFFSET(N21,COUNT(N1:N90),0,-7))

To average the LAST 6 values in the range N1:N90, it works well...

However some periods I want it to ignore, those that have an X in column A, for example might have

10
20
10
X  10
20
10
20
10
20

so last 6 values would be 10,20,10,20,10,20 but it will ignore the first 10 because there is an X in column A same line as the number.
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Excel & VBA ExpertCommented:
Hi,

You can try below, change the range accordingly:
``````=AVERAGEIF(\$A\$21:\$A\$90,"<>x",OFFSET(\$N\$21,COUNT(\$N\$21:\$N\$90),0,-6))
``````
Mirage_AverageIf-on-Condition.xlsx
Author Commented:
Hi

Thank you for that.  It doesn't seem to be averaging correctly.  If I manually select the values the average bottom right in excel not marry up with calculation for some reason
Excel & VBA ExpertCommented:
Check in the attached file above, add some more x in last cells in Col A, it will average correctly
Author Commented:
Just did getting same result still doesn't seem calculate properly for odd reason
ConsultantCommented:
Hi MirageSF,

Using Shums' first attempt, but amending, please try this in cell P22 of the file they posted above:

``````=AVERAGEIF(OFFSET(\$A\$21,COUNT(\$N\$21:\$N\$90),0,-6),"<>x",OFFSET(\$N\$21,COUNT(\$N\$21:\$N\$90),0,-6))
``````

I think the problem with Shums' formula is that the criteria range (Column A) was 70 rows deep, whereas the 'averaging' range was 6 rows deep.

However, I am wondering if this approach is not fundamentally flawed, since it reduces the number of items being averaged from six to five (with one 'x' in Column A)?

Perhaps you want the attached (I re-used Shums' data so that you can see the differences) with this formula calculating the average:

``````{=AVERAGEIF(INDIRECT("A"&LARGE(IF(A22:A30="x",0,ROW(A22:A30)),6)&":A"&COUNT(\$N\$21:\$N\$90)+21),"<>x",INDIRECT("N"&LARGE(IF(A22:A30="x",0,ROW(A22:A30)),6)&":N"&COUNT(\$N\$21:\$N\$90)+21))}
``````

That is an 'array entered' formula - enter it using Shift-Ctrl-Enter without the braces (or see the attached Version 2 file).

This always takes the last six items from the bottom up, but excludes the items with an 'x' in Column A.

If the data set moves elsewhere - either difference columns or rows - you will need to change it accordingly.

Hope that helps,

Alan.
EE-29079886-Mirage_AverageIf-on-Con.xlsx

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Finance AnalystCommented:

=SUMIF(OFFSET(Data!\$A\$1,COUNTA(Data!\$N\$1:\$N\$90)-6,0,6,1),"<>x",OFFSET(Data!\$N\$1,COUNTA(Data!\$N\$1:\$N\$90)-6,0,6,1))/COUNTIF(OFFSET(Data!\$A\$1,COUNTA(Data!\$N\$1:\$N\$90)-6,0,6,1),"<>x")

Uses the OFFSET function as previously seen to determine last 6 rows of columns A and N; both are using count of column N to determine extent of data. With those two ranges, the SUMIF then sums up the values in column N where column A does not equal X; the COUNTIF counts the values in column N where column A does not equal X. Average = SUMIF / COUNTIF

If column A will be blank or X then the "<>X" can be replaced with "".

If there are two X entries in column A within the last 6 rows, this will average the relevant 4 of the last 6.

Is this correct or should it then include two earlier values; ie the last 6 where A does not equal X.
Author Commented:
Ideally if it's possible to get it to do last 6 values where there is not an X that would be perfect.  As you stated earlier values.
With error checking in case it's none or just 1 value available. Thank you
Finance AnalystCommented:
Would you be OK with a VBA solution?

Thinking that it could be a user defined function (UDF) to find the last 6 and average them.

Is there other data in the columns B to M that might be relevant to identifying the last 6?

A sample file with those columns populated would be useful.
Author Commented:
Udf to find last 6 values of interest excluding X ranges would be great.  Would need two ranges passed to it the range of where X will be found usually A but might change and the range of numbers to scan for last 6 so way I see it udf("a21:a72","n21:n72") ? Thx
Finance AnalystCommented:
Sample file???

Where does the data start? Does it start at row 1 or row 21? How far down is potential for last row, row 72 or row 90?
Finance AnalystCommented:
If you are happy to be changing the inputs of the UDF, why can't you just change the inputs of an AVERAGEIFS statement?
Author Commented:
Data starts at 21 and ends at 72.  Not sure an averageifs would pick up 6 values even if there are x inbetween
Finance AnalystCommented:
Here's an alternative method:

In column O (or other spare column) use this formula, start in O2 and copy down to O90:
=IF(A2="X","",COUNTIFS(N2:N\$90,"<>",A2:A\$90,"<>X"))

This will number the rows in reverse order for the count of entries in column N where A does not equal X. Beyond the last entry in column N the value will be zero. The last entries in the column will be numbered from 1 to 6 where A is not equal to X.

If, as suggested in comments, the data starts in row 21 and could go to row 90, use this instead, starting in O21:
=IF(A21="X","",COUNTIFS(N21:N\$90,"<>",A21:A\$90,"<>X"))

Then the average calculation is as simple as:

=AVERAGEIF(O2:O90,"<="&6,N2:N90)
or
=AVERAGEIF(O21:O90,"<="&6,N21:N90)

If you want to adjust the number of values used for the average, amend the "<="&6 part of the AVERAGEIF formula, adjust the 6 or amend it to refer to a cell that contains the number of cells you wish to use.
Author Commented:
=IF(A2="X","",COUNTIFS(N2:N\$90,"<>",A2:A\$90,"<>X")) << When I do this is says #VALUE! all way down.
Finance AnalystCommented:
I think that would suggest there are error values in A or N
ConsultantCommented:
Hi MirageSF,

Did the solution I posted above work for you?

If it did that's great, but if not, please can you identify what is not working?

Thanks,

Alan.
Finance AnalystCommented:
If we're going to split points might as well split evenly.
Excel & VBA ExpertCommented:
No comment has been added to this question in more than 14 days, so it is now classified as abandoned.

If you feel this question should be closed differently, post an objection and a moderator will read all objections and then close it as they feel fit. If no one objects, this question will be closed automatically the way described above.
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.