Average If in Excel 2003

[nicolehalliday]

Hi,

I am trying to average if, however the function is not available in Excel 2003. I want to average values in range K4:K103, only if the corresponding value in the same row but in range E4:E103 equals 1. I have tried embedding an 'if' into an 'average':

=AVERAGE(IF($E$4:$E$103="1",K4:K103,FALSE))

I also thought about doing a vlookup. I would enter the vlookup in a separate worksheet or table and lookup each value in column K that has a 1 in column E. Then I would average all of those values and just link the average into my master spreadsheet. However, I am not very experienced in Excel and I couldn't think of a way to look up the NEXT value, and end up with each different value that meets the condition.

I also had the idea about using the offset function somewhere because basically i want to average all of the values in column E that are 1, except offset by 6 columns to the right.

I have spent much too long working on this simple function and would really appreciate some help!!!!!!!!!!!!! Thanks

 

[GlennUK]

That formula is fine, as long as you are entering using Ctrl-Shift-Enter instead of Enter.

 

[Richard Schollar]

Hi

Your attempt was nearly correct:

=AVERAGE(IF($E$4:$E$103=1,K4:K103))

You need to confirm this with Ctrl+Shift+Enter (it is an array formula) - following successful entry, Excel will surround the formula in the formula bar with curly braces {}.

Note - I removed the "" from around the 1 as I am assuming you have a numrical 1 in column E rather than a textual one.

 

[GlennUK]

... good catch on the "" part Richard

 

[nicolehalliday]

Hey thanks so much for the replies! Before my formula just returned a zero, but now with the CTRL+SHIFT+ENTR it returns a #N/A.

In my range E4:E103 I have a few cells that are #N/A. Could this be why? If so, is there a way to make my formula ignore these cells?

Thank you!

 

[mancemonster]

I was working on the basis of...

=SUMIF($E$4:$E$103,1,$K$4:$K$103)/COUNTIF($E$4:$E$103,1)

But your solution is more elegant

 

[Richard Schollar]

You could extend the array formula to:

=AVERAGE(IF(ISNUMBER($E$4:$E$103),IF($E$4:$E$103=1,K4:K103)))

but I rather like mancemonster's which should work as is

 

[nicolehalliday]

That works! I didn't think elegance existed in Excel formulas but people on this website certainly prove me wrong!

Thank a million for the help