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.
MirageSFAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

ShumsExcel & 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))

Open in new window

Mirage_AverageIf-on-Condition.xlsx
MirageSFAuthor 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
ShumsExcel & VBA ExpertCommented:
Check in the attached file above, add some more x in last cells in Col A, it will average correctly
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

MirageSFAuthor Commented:
Just did getting same result still doesn't seem calculate properly for odd reason
AlanConsultantCommented:
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))

Open in new window


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))}

Open in new window


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

Your issues matter to us.

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

Start your 7-day free trial
Rob HensonFinance AnalystCommented:
Without array function, how about this:

=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.
MirageSFAuthor 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
Rob HensonFinance 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.
MirageSFAuthor 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
Rob HensonFinance 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?
Rob HensonFinance 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?
MirageSFAuthor Commented:
Data starts at 21 and ends at 72.  Not sure an averageifs would pick up 6 values even if there are x inbetween
Rob HensonFinance 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.
MirageSFAuthor Commented:
=IF(A2="X","",COUNTIFS(N2:N$90,"<>",A2:A$90,"<>X")) << When I do this is says #VALUE! all way down.
Rob HensonFinance AnalystCommented:
I think that would suggest there are error values in A or N
AlanConsultantCommented:
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.
Rob HensonFinance AnalystCommented:
If we're going to split points might as well split evenly.
ShumsExcel & 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.