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("'d ata'!" & srt) 0))

=average(if(b2.b5000))

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")

--


Gary


"inquirer" wrote in message
...

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("'d ata'!" & srt) 0))

=average(if(b2.b5000))

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

or this
Range("a1").FormulaArray = "=average(if(b2:b5000,b2:b500))"

--


Gary


"inquirer" wrote in message
...

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("'d ata'!" & srt) 0))

=average(if(b2.b5000))

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

or even

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
or this
Range("a1").FormulaArray = "=average(if(b2:b5000,b2:b500))"

--


Gary


"inquirer" wrote in message
...

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("'d ata'!" & srt)
0))

=average(if(b2.b5000))

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

inquirer wrote:

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("'d ata'!" & srt) 0))

=average(if(b2.b5000))

works in excel and I'd like the equivqlent in vba if possible
Thanks
Chris
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