IF/Index/Average

**agraham** · 01-15-2016, 10:50 AM

Hello,

I need to take the average of values in a column if the values in two other columns meet some criteria. For the attached example, I want column G to return a 1 if the value in column E is in the bottom 3 quartiles of all the values for a given item in column A. I had this IF statement set up to do that on a more manual basis =IF(AND(E4>=QUARTILE.INC($E$4:$E$30,0),E4<=QUARTILE.INC($E$4:$E$30,3)),1,0), but I want the formula to query on the item number, not to have to manually set the parameters for each item and corresponding array. I hope I've explained this adequately.

Book2.xlsx

**Bernie Deitrick** · 01-15-2016, 01:06 PM

You can calculate the value you want using a column of array formulas (single cell array formulas, copied down the column, not array-entered into multiple cells):

In a cell in row 4, array-enter (enter using Ctrl-Shift-Enter) the formula

=My formula cannot be posted for some reason

And copy down to match your data.

Here is your file, modified to work:

Book2.xlsx

**Bernie Deitrick** · 01-15-2016, 01:10 PM

=if(countif(a$1:a4,a4)=1,average(if(a:a=a4, if(e:e < quartile( if( a:a=a4, e:e),3), e:e))), "")

There was something about the original formula that the software did not like..... strange. I had to put spaces in, and then it took the formula, but changed all the caps to lowercase....

**agraham** · 01-15-2016, 03:15 PM

That was perfect, thanks so much!!

IF/Index/Average

LinkBack

Thread Tools

Rate This Thread

Display

IF/Index/Average

Re: IF/Index/Average

Re: IF/Index/Average

Re: IF/Index/Average

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Vlookup, Index and Average..., can this be done?

average with index match

average index match

Index with sum and average

Index/Match to find Starting Point of Average, Ending Point of Average Variable

[SOLVED] How to average all rows with same index value ?

Trying to take an average using INDEX and/or MATCH

Bookmarks

Bookmarks

Posting Permissions