average of values >0

  • Thread starter Thread starter inquirer
  • Start date Start date
I

inquirer

Is there a way to make the average funtion work in vba so that it
selects only values >0 in a given range?
Something like
avv = Application.WorksheetFunction.Average(if(Range("'data'!" & srt) > 0))

=average(if(b2.b500>0))

works in excel and I'd like the equivqlent in vba if possible
Thanks
Chris
 
Hi Chris,

you can use this:
=worksheetfunction.SumIf(selection,">0",selection)/worksheetfunction.CountIf(selection,">0")

replace selection with range you wish

regards,
Ivan
 
how about something like this?

Range("a1").Value = WorksheetFunction.Sum(Range("b2:b500")) / _
WorksheetFunction.CountIf(Range("b2:b500"), ">0")
 
or even

Range("a1").Value = Evaluate("=average(if(b2:b500>0,b2:b500))")

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
inquirer said:
Is there a way to make the average funtion work in vba so that it
selects only values >0 in a given range?
Something like
avv = Application.WorksheetFunction.Average(if(Range("'data'!" & srt) > 0))

=average(if(b2.b500>0))

works in excel and I'd like the equivqlent in vba if possible
Thanks
Chris
Click to expand...

I guess I didn't make myself clear... what I was looking for two things:
how to put a named range into the worksheet function (the way I was
doing it doesn't seem to work), and
how to use a conditional statement in the function.

Ivan's solution is fine for getting averages but what about other
functions like stdev and var?

Thanks
Chris
 
inquirer said:
Is there a way to make the average funtion work in vba so that it
selects only values >0 in a given range?
Something like
avv = Application.WorksheetFunction.Average(if(Range("'data'!" & srt) > 0))

=average(if(b2.b500>0))

works in excel and I'd like the equivqlent in vba if possible
Thanks
Chris
Click to expand...
I guess I didn't make myself clear... what I was looking for two things:
how to put a named range into the worksheet function (the way I was
doing it doesn't seem to work), and
how to use a conditional statement in the function.

Ivan's solution is fine for getting averages but what about other
functions like stdev and var?

Thanks
Chris
 
Back
Top