# Averageif help

#### Bedford

##### Active Member
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.

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

#### AhoyNC

##### Well-known Member
Like this?
Code:
``'=AVERAGEIF(T4:T709,"cad",AL4:AL709)``

#### Bedford

##### Active Member
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

##### Well-known Member
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).

#### Bedford

##### Active Member
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

##### Well-known Member
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

##### Active Member
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

##### Well-known Member
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
ABCDEF
20tyu***5
Sheet

#### Bedford

##### Active Member
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

##### Board Regular
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.

Replies
3
Views
475
Replies
20
Views
2K
Replies
9
Views
387
Replies
25
Views
501
Replies
3
Views
309

1,191,718
Messages
5,988,275
Members
440,146
Latest member
rgomes8

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back