Worksheet Function - Calculations 2

[VincentCrimmins]

Hi,

I have a list of values, both positive and negative ranging between -5 and +5, defined in VBA as:

DailyPerf = Range("B2:B" & LastRowDailyPerf)

I've calculated the Average as follows:

Cells(20, 7) = Excel.WorksheetFunction.Avg(DailyPerf)

I want to calculate the average of the positive values. Is it possible to do this using the format for calculating the Average in Cells(20,7)?

Thanks!

 

[SkipVought]

[b]
Set [/b]DailyPerf = Range("B2:B" & LastRowDailyPerf)
Cells(20, 7).value = Excel.WorksheetFunction.Avg([DailyPerf])

Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]

 

[combo]

Maybe direct definition: SumIF(...)/CountIf(...)

combo

 

[mintjulep]

Something like:

Count=0
running=0

For each cell in range

if cell.value > 0 then
count = count + 1
running = running + cell.value
end if

PosAvg = running/count

I'll leave working out the actual syntax to you.

 

[VincentCrimmins]

Hi Skip,

I want to calculate the average of the positive values only.

If I was to do this in Excel I'd put it in a cell as:

{=AVERAGE(IF('Daily Perf'!B3:B1925>0,'Daily Perf'!B3:B1925))}

But I want to code this in VBA, similar to:

Cells(20, 7) = Excel.WorksheetFunction.Avg(DailyPerf)

Any ideas?

Thanks for your help...

 

[VincentCrimmins]

Hi Combo,

I tried: Cells(7, 7) = Excel.WorksheetFunction.SumIf(DailyPerf, ">0", DailyPerf)

but it's telling me that an object is required, can you see what's wrong with my syntax?

Thanks...

 

[combo]

Have you set DailyPref range for vba (Skip's first post)?BTW, there is no need for third argument in SumIf.

combo

 

[SkipVought]

Check out the SUBTOTAL function with the AVERAGE operator. Use the AutoFilter to display only positive values

Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]

 

[yogia]

Hi VincentCrimmins:

You may also want to try ...

Set refRange = Range("B2:B" & LastRowDailyPerf)
    ActiveWorkbook.Names.Add Name:="yRefRange", RefersTo:=refRange
    Cells(20, 7).FormulaArray = "=Average(if(yrefRange>0,yrefRange))"

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com