How do i write correct formula

[PMRetired2012]

Hello
The formula im trying to write is a formula like this one : =IF(ISNUMBER(AVERAGEIF(B22:AL22,">0")),AVERAGEIF(B22:AL22,">0"),"0") except where there is one range (B22:AL22) i want to use 3 ranges which are 3 diffrent rows( B4:AL4, B6:AL6,B8:AL8) All im really doing is adding 3 ranges after the average if and before the ">0" on both sides of formula.

Dennis

 

[jasonb75]

Are there numbers anywhere in B5:AL5 or B7:AL7? If not then you could use

=AVERAGEIF(B4:AL8,">0")

 

[Peter_SSs]

You might not want to do it with 20 rows but with only 3 it isn't too bad to do it 'long hand'

=(SUMIF(B4:AL4,">0")+SUMIF(B6:AL6,">0")+SUMIF(B8:AL8,">0"))/(COUNTIF(B4:AL4,">0")+COUNTIF(B6:AL6,">0")+COUNTIF(B8:AL8,">0"))

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Fluff]

Duplicate to: Average Formulas with 3 ranges to average as data is added weekly

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.