Averageif help

Bedford

Active Member
Joined
Feb 3, 2015
Messages
316
Office Version
  1. 365
Platform
  1. MacOS
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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Like this?
Code:
'=AVERAGEIF(T4:T709,"cad",AL4:AL709)
 
Upvote 0
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.
 
Upvote 0
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))
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
1-1cad6.33322242
20tyu***5
36cad*44263
4*cad*102100*
514cad*50222
Sheet
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top