Averageif help

[Bedford]

Very much an excel novice, using mac version 2011, am attempting to gather average percentage in range AL4:AL709 based on criteria "CAD" in range T4:T709. There are other criteria in the range T4:T709, but I'm hoping to extract only the sum of average percentages from the AL column as they relate to "CAD" in the T column.
Any help would be much appreciated.
Thanks,
Doug.

 

[AhoyNC]

Like this?

'=AVERAGEIF(T4:T709,"cad",AL4:AL709)

 

[Bedford]

I did try that, ended up with a #DIV/0! error, so i tried, =IFERROR(AVERAGEIF(T4:T709, "CAD" , AL4:AL709),0), and that returned nothing? very confused.

 

[AhoyNC]

Are they actual numbers in range AL4:AL709 or are they text? Does AVERAGE(AL4:AL709) return a #DIV/0! error?

What happens when you enter this array formula? NOTE: you must enter the formula below with CTRL-SHIFT-ENTER (command-return on a MAC).
=AVERAGE(IF(T4:T709="CAD",AL4:AL709*1))

 

[Bedford]

Thanks for your response. So entering =AVERAGE(AL4:AL709) does return the #DIV/0! error, and the range AL4:AL709 actually has a formula; =IFERROR(($Z701-$AJ701)/$Z701,"") that returns a percentage, a number.
I did try =AVERAGE(IF(T4:T709="CAD",AL4:AL709*1)), and pressed (command-shift) which seems to bring the "curly" brackets around the formula, but this formula returns the error; #VALUE!

 

[AhoyNC]

The array formula should have curly brackets. The only reason for that formula was to see if you had text values and turn them into numeric values.
Both the AVERAGEIF and AVERAGE formula should have returned a number unless there is an error in all the cells.
Try removing the IFERROR from your formula ($Z701-$AJ701)/$Z701 to see what error and how many you are getting.

 

[Bedford]

When removing the IFERROR, I now get the #DIV/0! error, only in cells that have nothing to calculate in the "Z" and "AJ" cells as they're blank. If the "Z" and "AJ" cells have a result, it produces a result without error.

 

[AhoyNC]

I'm not sure at this point. I tried to produce the error (see below) with a #DIV/0! in cell A4, but the AVERAGEIF formula worked for me.

Excel Workbook
	A	B	C	D	E	F
1	-1	cad	6.333	22	24	2
2	0	tyu	*	*	*	5
3	6	cad	*	44	26	3
4	*	cad	*	102	100	*
5	14	cad	*	50	22	2
Sheet

 

[Bedford]

Honestly, I can't explain it, and that's really no surprise as I'm a complete novice with formulas in Excel, I'd like to learn more, time is hard to come by...
Thanks again for all your help, really appreciate folks on this site who take the time to help others.
Doug.

 

[seekerarcane]

I have checked AVERAGEIF formula on your data with IFERROR formula on range A1:A5, its works perfectly fine with me, given perfectly fine results.