How to use If statement with data that is filtered using AutoFilter?

[fbarbie]

I can use SUBTOTAL to make calculations using autofiltered data. However, I want to calculate an average on a filtered subset of the data that meet a condition in another field. How can I do that with filltered data?

[Domenic]

Assuming that Column B is used for your criteria in the filtered subset of data, and that Column C contains the values you want averaged, try the following...

=AVERAGE(IF((SUBTOTAL(3,OFFSET(RangeC,ROW(RangeC)-MIN(ROW(RangeC)),0,1)))*(RangeB="Criteria"),RangeC))

...confirmed with CONTROL+SHIFT+ENTER. If your criteria is a numerical value, remove the quotes.

Hope this helps!

[fbarbie]

Domenic,

Thank you. This worked perfectly. How can I do this same thing, but I want the average of all values in more than one column that are not adjacet to one another? I know if they were adjacent, I could do something like A1:C5000, but how would the formula you provided look if I wanted to calculate the average of values in column C, E and G? Thank you again for your help.

Frank

[Domenic]

Try...

=AVERAGE(IF((SUBTOTAL(3,OFFSET(RangeA,ROW(RangeA)-MIN(ROW(RangeA)),0,1)))*(RangeB="Criteria")*(MOD(COLUMN(RangeC:G)-MIN(COLUMN(RangeC:G)),2)=0),RangeC:G))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

[fbarbie]

What is RangeA supposed to be?

Also, I see that you put C:G because I want to calculate the averages among C, E and G. What about the columns in between? They are text so maybe they will not be included in the calculations. Is that correct?

Thanks.

[Domenic]

Sorry for not being too clear. The formula excludes Columns D and F from its calculation, even though these columns are referenced. The MOD argument effects this exclusion. So your formula would look something like this...

=AVERAGE(IF((SUBTOTAL(3,OFFSET(B2:B100,ROW(B2:B100)-MIN(ROW(B2:B100)),0,1)))*(B2:B100="Criterion")*(MOD(COLUMN(C2:G100)-MIN(COLUMN(C2:G100)),2)=0),C2:G100))

But if Columns D and F contain only text values, then there would be no need for the MOD argument, since AVERAGE ignores text values. In this case, your formula would look something like this...

=AVERAGE(IF((SUBTOTAL(3,OFFSET(B2:B100,ROW(B2:B100)-MIN(ROW(B2:B100)),0,1)))*(B2:B100="criterion"),C2:G100))

Hope this helps!

[fbarbie]

Hmmm...I tried this formula and a few variations and I get a #DIV/0 error. Below is the formula I tried.

{=AVERAGE(IF((SUBTOTAL(3,OFFSET(raw!AB2:raw!AR5000,ROW(raw!AB2:raw!AR5000 )-MIN(ROW(raw!AB2:raw!AR5000)),0,1)))*(raw!U2:raw!U5000="1"),raw!AB2:raw!AR5000))}

Any suggestions?

[Domenic]

If Column U contains numeric values, as I suspect, remove the quotes from its criterion...

...(raw!U2:raw!U5 000=1)...

Does this help?

[fbarbie]

Yes, thank you. Forgot to remove the quotes. It works correctly now.

This should be the final thing: I use this same formula, but with an array in the criterion rather than just one value. Below is the formula:

{=AVERAGE(IF((SUBTOTAL(3,OFFSET(raw!$U$2:raw!$U$65000,ROW(raw!$U$2:raw!$U$65000)-MIN(ROW(raw!$U$2:raw!$U$65000)),0,1)))*( raw!$U$2:raw!$U$65000={2,3,4,5}),raw!AB2:AR65000))}

Although this formula works when I put just one value, I get an #N/A error when I put in an array of values. Do you have any ideas of why it is not working?

I want the formula to calculate the average using values in AB through AR if they have a 2,3,4 or 5 in column U.

Domenic, thank you again for your help. I have learned a lot, but hopefully this will be the last time.

[Domenic]

That's because the arrays are different sizes. ( raw!$U$2:raw!$U$65000={2,3,4,5}) sets up an array that is 4 columns wide, while raw!AB2:AR65000 sets up an array that is 17 columns wide. Try the following formula instead...

=AVERAGE(IF((SUBTOTAL(3,OFFSET(raw!$U$2:$U$65000,ROW(raw!$U$2:$U$65000)-MIN(ROW(raw!$U$2:$U$65000)),0,1)))*(ISNUMBER(MATCH(raw!$U$2:$U$65000,{2,3,4,5},0))),raw!AB2:AR65000))

...confirmed with CONTROL+SHIFT+ENTER.

...but hopefully this will be the last time.

Hopefully not! Ask as many questions as you'd like. There's always somebody eager to help, provided they have a solution to offer.

[fbarbie]

Yes, that works. Thank you very much!

It always amazes me how Excel does all this crazy stuff. And thank you for all your help. Did you take courses in Excel or did you learn by yourself?

[fbarbie]

Just when I thought it was all over.

Ok, the formula correction you suggested works. Now I want to put these formulas in other cells and change some cell refernces. When I do this, the resulting number is incorrect. Here is the working formula and it is in cell C2:

{=AVERAGE(IF((SUBTOTAL(3,OFFSET(raw!$U$2:raw!$U$65000,ROW(raw!$U$2:raw!$U$65000)-MIN(ROW(raw!$U$2:raw!$U$65000)),0,1)))*(raw!$U$2:raw!$U$65000=1),raw!$AB$2:$AR$65000))}

Here is the formula for an array of numbers that is in cell C7. This one works fine too:

=AVERAGE(IF((SUBTOTAL(3,OFFSET(raw!$U$2:$U$65000,ROW(raw!$U$2:$U$65000)-MIN(ROW(raw!$U$2:$U$65000)),0,1)))*(ISNUMBER(MATCH(raw!$U$2:$U$65000,{2,3,4,5},0))),raw!$AB$2:$AR$65000) )

Now here are the formulas that produce errors. The first is in cell C20 and mimics the first formula above except for the last reference:

{=AVERAGE(IF((SUBTOTAL(3,OFFSET(raw!$U$2:raw!$U$65000,ROW(raw!$U$2:raw!$U$65000)-MIN(ROW(raw!$U$2:raw!$U$65000)),0,1)))*(raw!$U$2:raw!$U$65000=1),raw!$BL$2:$CB$65000))}

This formula is in cell C25 and mimics the second formula above except for the cell reference. Thanks again!

[Domenic]

Try...

=AVERAGE(IF((SUBTOTAL(3,OFFSET(raw!$U$2:$U$65000,ROW(raw!$U$2:$U$65000)-MIN(ROW(raw!$U$2:$U$65000)),0,1)))*(raw!$U$2:$U$65000=1)*(raw!$BL$2:$CB$65000>0),raw!$BL$2:$CB$65000))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

[fbarbie]

Ok, here is a new problem in this situation. All the formulas covered in this thread have worked perfectly. They are all used to present summaries of raw data on another worksheet.

Now, I want to add more data to the raw data sheet for additional analyses. However, when I add data, some of the formulas cease to work and I get a #VALUE error. Below are the formulas that are doing this:

{=AVERAGE(IF((SUBTOTAL(3,OFFSET(raw!$U$2:$U$64999,ROW(raw!$U$2:$U$64999)-MIN(ROW(raw!$U$2:$U$64999)),0,1)))*(ISNUMBER(MATCH(raw!$U$2:$U$64999,{2,3,4,5},0))),raw!$AB$2:$AR$64999))}

{=AVERAGE(IF((SUBTOTAL(3,OFFSET(raw!$U$2:$U$64999,ROW(raw!$U$2:$U$64999)-MIN(ROW(raw!$U$2:$U$64999)),0,1)))*(ISNUMBER(MATCH(raw!$U$2:$U$64999,{2,3,4,5},0)))*(raw!$AT$2:$BJ$64999>0),raw!$AT$2:$BJ$64999))}

So, when the original data that was present when I added these formulas is there, the formulas work fine. When I add new data, they get a #VALUE error. Please let me know what could be the problem. Thanks.

[Domenic]

Make sure that your actual formula has the same size ranges.

Does this help?

[fbarbie]

Yes, they are always 17 columns and go from rows 2-64999. Now here is the kicker. The two formulas I just posted, the first is in cell C7, the second in C16. There are two other formulas that are identical to these that work just fine. The only differences are the cells that are included in the range for calculation. They are in cells C25 and C34 and they are below:

{=AVERAGE(IF((SUBTOTAL(3,OFFSET(raw!$U$2:$U$64999,ROW(raw!$U$2:$U$64999)-MIN(ROW(raw!$U$2:$U$64999)),0,1)))*(ISNUMBER(MATCH(raw!$U$2:$U$64999,{2,3,4,5},0)))*(raw!$BL$2:$CB$64999>0),raw!$BL$2:$CB$64999))}

{=AVERAGE(IF((SUBTOTAL(3,OFFSET(raw!$U$2:$U$64999,ROW(raw!$U$2:$U$64999)-MIN(ROW(raw!$U$2:$U$64999)),0,1)))*(ISNUMBER(MATCH(raw!$U$2:$U$64999,{2,3,4,5},0)))*(raw!$CD$2:$CT$64999>0),raw!$CD$2:$CT$64999))}

This is the frustrating part. They are all the same formula with just the cell references changed yet half work and half do not. This definitely deserves a :P.

Thanks

Frank

[Domenic]

Try this and see if it helps...

1) Select/highlight an empty cell

2) Copy > Edit

3) Select your data/columns containing numerical values

4) Edit > Paste Special > Add > Ok

Does this help?

[fbarbie]

I selected any empty cell in the calculation page then pressed copy. Then went to the worksheet with the raw data, selected the 9 columns where the numerical values are (AB:AR, I tried both selecting all and selecting just the columns with numbers). Then clicked paste special, add, ok.

I'm not sure what is supposed to happen, but when I went back to the problem formulas, I clicked F2 then CTRL+SHFT+ENTER and they still have #VALUE errors.

Is there something else I'm supposed to do?

Domenic, I just wanted to mention again that these formulas mess up only when I add new data. When I take out the new data and only the data that was there when the formulas were put in place is present, it works.

[Domenic]

If you'd like, you can email me your file (removing any personal information) at domenic22@sympatico.ca and I'll see if I can figure out what's going on.

[Domenic]

Well, I tried filtering for different criteria, and I tried entering new data, and at no time did I receive a #VALUE error. Also, I checked the results for your formulas in the cells highlighted purple and they seem fine, except for C7 and C8. These formulas should be changed to exclude zero vaulues. Blanks cells are considered to have a zero value and is included in the calculation. Therefore, change the formulas as follows...

C7:

=AVERAGE(IF((SUBTOTAL(3,OFFSET(raw!$U$2:$U$64999,ROW(raw!$U$2:$U$64999)-MIN(ROW(raw!$U$2:$U$64999)),0,1)))*(ISNUMBER(MATCH(raw!$U$2:$U$64999,{2,3,4,5},0)))*(raw!$AB$2:$AR$64999>0),raw!$AB$2:$AR$64999))

C8:

=AVERAGE(IF((SUBTOTAL(3,OFFSET(raw!$U$2:$U$64999,ROW(raw!$U$2:$U$64999)-MIN(ROW(raw!$U$2:$U$64999)),0,1)))*(ISNUMBER(MATCH(raw!$U$2:$U$64999,{1,2,3,4,5},0)))*(raw!$AB$2:$AR$64999>0),raw!$AB$2:$AR$64999))

Both formulas to be confirmed with CONTROL+SHIFT+ENTER. Can you have another look at the file you sent me and see if you are in fact getting an error value? If it checks out, then the problem may lie with your original file.

[fbarbie]

Domenic,

First of all, the changes you suggested for the formulas were helpful. Thank you.

Second, I took the comments out of the file I sent you and that seems to be the cause of the problems. I am emailing you the file with comments for individuals in the company called PSP. It is the same data as the D&D company but this time it has comments. You will see that now, the following occurs:

Choose staff lim: the leading self and leading others formulas go wrong
Choose cb change: the leading others and leading the organization formulas go wrong
Choose bali pontiac: all of the work fine

[Domenic]

Bingo! Found the culprit! The reason the formulas return an error value is because one or more cells in your source data have text whose length exceeds the limit. I think the limit is 255 characters. So, in this case, you can change the formulas as follows...

=AVERAGE(IF(MOD(COLUMN(raw!$AB$2:raw!$AR$64999)-MIN(COLUMN(raw!$AB$2:raw!$AR$64999)),2)=0,IF((SUBTOTAL(3,OFFSET(raw!$U$2:$U$64999,ROW(raw!$U$2:$U$64999)-MIN(ROW(raw!$U$2:$U$64999)),0,1)))*(ISNUMBER(MATCH(raw!$U$2:$U$64999,{2,3,4,5},0)))*(raw!$AB$2:$AR$64999>0),raw!$AB$2:$AR$64999)))

...confirmed with CONTROL+SHIFT+ENTER. Also, you can hide #DIV/0! values by using conditional formatting...

1) Select/highlight C2

2) Format > Conditional Formatting > Formula Is

3) Enter the following formula:

=ISERR(C2)

4) Choose 'White' as your font

5) Click Ok

6) Use 'Format Painter' or 'Copy > Paste Special > Formats' to copy the formatting to other cells.

You may also want to use dynamic named ranges instead of near whole column references. It may speed things up a bit. If you need help, post back.

Hope this helps!

[RxMiller]

Assuming that Column B is used for your criteria in the filtered subset of data, and that Column C contains the values you want averaged, try the following...

=AVERAGE(IF((SUBTOTAL(3,OFFSET(RangeC,ROW(RangeC)-MIN(ROW(RangeC)),0,1)))*(RangeB="Criteria"),RangeC))

...confirmed with CONTROL+SHIFT+ENTER. If your criteria is a numerical value, remove the quotes.


I use the =SUBTOTAL(104,W5:W580) to only show the subtotal of the autofilter results.
A field V has "Approved", "Denied", "Rejected"...
So, would the 3 above be replaced by the 104?

Excel 2007

[Domenic]

Within a filtered list, to return the largest value in Column W, where the corresponding value in Column V equals 'Approved', try...

=MAX(IF(SUBTOTAL(3,OFFSET(W5:W580,ROW(W5:W580)-ROW(W5),0,1)),IF(V5:V580="Approved",W5:W580)))

...confirmed with CONTROL+SHIFT+ENTER.