Average based on conditions in array

[rize1159]

I want to average only those values which are greater than 0. pls modify this formula for the same

=AVERAGE($D$23:$AA$23)*3

THis is giving resutls of all cells either empty or containig zero. BUt I want to average only those which are greater than zero

 

[ilovepurple]

try

=(SUMIF($D$23:$AA$23, ">0")/COUNTIF($D$23:$AA$23, ">0"))*3

 

[rize1159]

try

=(SUMIF($D$23:$AA$23, ">0")/COUNTIF($D$23:$AA$23, ">0"))*3

Thanks a lot

 

[T. Valko]

I want to average only those values which are greater than 0. pls modify this formula for the same

=AVERAGE($D$23:$AA$23)*3

THis is giving resutls of all cells either empty or containig zero. BUt I want to average only those which are greater than zero

Try one of these...

If you're using Excel 2007 or later:

=AVERAGEIF($D$23:$AA$23,">0")*3

This array formula** will work in any version of Excel:

=AVERAGE(IF($D$23:$AA$23>0,$D$23:$AA$23))*3

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.