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.

 

[Bedford]

Thanks for weighing in "seekerarcane". I think having a closer look at the formula I posted earlier, =IFERROR(($Z4-$AJ4)/$Z4,""), and the formula quoted by AhoyNC, =IFERROR((D1-E1)/F1,""), there is a slight difference. I'm trying to calculate the percentage of "Gross Margin", which is =((selling price-cost of goods)/selling price). To have the formula by AhoyNC mimic the formula for "Gross Margin" it would look more like; =IFERROR((D1-E1)/D1,""). The formula I've been using =IFERROR(($Z4-$AJ4)/$Z4,"") works perfectly fine, however the AVERAGEIF formula is only showing an error when I have no entries, once I begin entering data and the column with the IFERROR formula has at least one entry then the AVERAGEIF returns a result void of error.
So, safe to say the worksheet will have entries, it just through me off when I first started building it and saw the error generated by AVERAGEIF when there were no entries.

 

[Aladin Akyurek]

Concisely, what is the formula that works for you when there is sufficient data?

 

[Bedford]

Either of the following work, when there is sufficient data;
=AVERAGEIF(T4:T709,"USD",AL4:AL709)
{=AVERAGE(IF(T4:T709="USD",AL4:AL709*1))}

 

[Aladin Akyurek]

Either of the following work, when there is sufficient data;
=AVERAGEIF(T4:T709,"USD",AL4:AL709)
{=AVERAGE(IF(T4:T709="USD",AL4:AL709*1))}

Whichever suits your needs...

1. just enter:

=SUMIF(T4:T709,"USD",AL4:AL709)/MAX(1,COUNTIF(T4:T709,"USD"))

2. control+shift+enter, not just enter:

=IF(ISNUMBER(1/IF(T4:T709="usd",AL4:AL709)),AVERAGE(IF(T4:T709="usd",AL4:AL709)),"")

 

[Bedford]

EUREKA! The #1 formula works, I've not tried the second, but, brilliant, thank you very, very much...
Doug.

 

[Aladin Akyurek]

EUREKA! The #1 formula works, I've not tried the second, but, brilliant, thank you very, very much...
Doug.

You are welcome.