# AverageIf of two columns

1. ## AverageIf of two columns

Hi,

I have a question about using an AverageIf for two different columns. Let me try to explain. I am using the two formulas below to get a last 10 averageif in each column:

=IFERROR(AVERAGE(IF(\$G\$2:G2=G3,IF(ROW(\$G\$2:G2)>=LARGE(IF(\$G\$2:G2=G3,ROW(\$G\$2:G2)),10),\$BW\$2:BW2))),"")
=IFERROR(AVERAGE(IF(\$P\$2:P2=P3,IF(ROW(\$P\$2:P2)>=LARGE(IF(\$P\$2:P2=T3,ROW(\$P\$2:P2)),10),\$BX\$2:BX2))),"")

So, the same set of people are in both columns, but I am averaging, let's say, day shift in one column and night shift in the other. I would like to combine the two and just get an overall average of the last 10 for any person from both columns. I tried this:
=IFERROR(AVERAGE(IF(\$G\$2:P2=G3,IF(ROW(\$G\$2:P2)>=LARGE(IF(\$G\$2:P2=G3,ROW(\$G\$2:P2)),10),\$BW\$2:BX2))),"")

Obviously, that did not work. Can anyone let me know how this can be done? Thanks  Register To Reply

2. ## Re: AverageIf of two columns

Hi dt32,

can you please upload a sample small file for the same.. as your formula is full of Relative & Absolute ref mixture..  Register To Reply

3. ## Re: AverageIf of two columns Originally Posted by Debraj Roy Hi dt32,

can you please upload a sample small file for the same.. as your formula is full of Relative & Absolute ref mixture..
Hmm..I can try but it would take me a while. The file I am using is people based and has salary information, so I would rather not, however. If it is necessary, I will put together an example.  Register To Reply

4. ## Re: AverageIf of two columns Originally Posted by Debraj Roy Hi dt32,

can you please upload a sample small file for the same.. as your formula is full of Relative & Absolute ref mixture..
OK.. i replaced the names with cities and deleted all sensitive information for this attachment. I did notice that I get a result if I delete column D. These two are not right next to each other in the overall spreadsheet though, so I added the blank to illustrate my problem. I can't add an attachment at work so I have added the dropbox link.

https://www.dropbox.com/s/x3gjtqj9mh...20example.xlsx  Register To Reply