AverageIF help, specific cells in multiple columns

[pwrisley]

Hi all,

New to the forum and couldn't find anything on this one. I have the following basic average formula that I need to add some sort of IF to.

=AVERAGE(I3,I8,I13,I21,I27)

Goal is to take the above and only include cells that = 100% in cells E8, E13, E21, E27. Basically, I only want the averages in the I column to compute if the corresponding E column is 100%.

Hope that makes sense, thanks for any help you can provide. Much appreciated.

 

[JPARKHURST]

Try AverageIF(Range, Crit, AvgRange)

The only 'problem' is that your data appears to be non-sequential.

What is on, say I2, I4, etc.? Is it data or blank?

If you can use the cells (if they don't have data in them):

Averageif(I:I,1,E:E).

If you cannot we'll have to work on something else.

 

[pwrisley]

Thank you JPARKHURST, unfortunately, the cells not referenced (I2, I4, etc.) are blank.

 

[JPARKHURST]

Ah, you're golden, then. Just use the range. I:I or (depending on how much data you have and again if it is static) I3:I27.

The most simple version: Averageif(I:I,1,E:E).

The blank cells don't matter, because their corresponding cell (E2/I2 combo) does not equal 1 (or 100%), so they will not be counted in the average.

 

[pwrisley]

Excellent, works perfectly. Thank you so much my friend.

 

[JPARKHURST]

Anytime.

 

[pwrisley]

Ok, I've been asked now to include one specific cell in the equation (E13) in the average when that cell is at least 1%. Summary: The current averageif(E:E,1,I:I) works great but now I have to add in a condition to add cell E13 if it is at least 1%. Any thoughts?

 

[JPARKHURST]

Are your cell addresses stable? Do they increase/decrease in how many cells you are checking? Are they always in the same location?

 

[pwrisley]

I'm in the process of building out a "review questionnaire" so there may be some additions and deletions to the cells over time. There will only be 5 cell locations in column E, however that I will need to include (currently, E3, E8, E13, E21, E27). It is the currently E13 location that I now need to add into the formula IF it is at least 1% and above. Sorry for the newbie response and if I'm not answering the questions clearly .

 

[Aladin Akyurek]

E3 = 7%, I3 = 4
E8 = 100%, I8 = 5
E13 = 1%, I18 = 6
E21 empty, I21 = 10
E27 = 40%, I27 = 6

Given the above data, what is the result which must obtain?