AVERAGEIF Function?

[Tyler_Durden]

Hi,

I need something like AverageIf function that basically takes the B column and averages only the numbers that are not 0… I have messed around with this and finally just got frustrated, maybe its because I’m using Excel 2003? Thanks!

[NBVC]

You can use:

=AVERAGE(IF(B1:B585>0,B1:B585))

confirmed with CTRL+SHIFT+ENTER and cannot use whole column references..

or you can use

=SUMIF(B:B,">0")/COUNTIF(B:B,">0")

confirmed with just ENTER and can use whole column references

[Shijesh Kumar]

Please Login or Register  to view this content.

[Tyler_Durden]

Thank you, the SUMIF worked... I should have included this... I'm incorporating this function into the code below to replace the plain AVERAGE, how would I change that SUMIF for the AVERAGE code?

Dim ws1 As Worksheet
Dim LR1 As Long, LC1 As Long
Dim rFind1 As Range

For Each ws1 In Worksheets(Array("TICKER", "CUSIP"))
ws1.Activate
LC1 = Cells(6, Columns.Count).End(xlToLeft).Column 'Last column of data
On Error Resume Next
Set rFind1 = Cells.Find(What:="Grand", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole)
If Not rFind1 Is Nothing Then 'Last row of data
LR1 = rFind1.Row
Else
LR1 = Range("A" & Rows.Count).End(xlUp).Row + 1
End If

Rows(LR1 & ":" & Rows.Count).Clear

Range(Cells(LR1, "B"), Cells(LR1, LC1)).FormulaR1C1 = "=SUM(R6C:R" & LR1 - 1 & "C)"
If ws1.Name = "CUSIP" Then Cells(LR1, "P").FormulaR1C1 = "=AVERAGE(R6C:R" & LR1 - 1 & "C)" Cells(LR1, "A").Value = "TOTALS"
With Range(Cells(LR1, "A"), Cells(LR1, LC1))
.Font.Bold = True

End With
Next ws1

[NBVC]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

and then hopefully someone can help you.. I am not sure I can decipher your code well enough to be of constructive help.

[Tyler_Durden]

I appologize, I'm new to this.... Hopefully this helps! Thanks again for your help

Please Login or Register  to view this content.

Next ws1

[NBVC]

if you enter that formula in a cell, then start recording a macro, then go to that cell, hit F2 and hit Enter.. stop the recording.

Then look at the macro it produces...

something like:

Please Login or Register  to view this content.

this will give you an idea fo the syntax...

[Tyler_Durden]

Thank you...

It seems to be freaking out because of that "C" towards the end of the second line, saying expected end of statement... Now I recieved this from another helpful memeber so I'm not exactly sure what its doing, all I know is if I take it out it doesn't use this function... sorry to be a pain about this just stuck on this minor point!

Please Login or Register  to view this content.

[Tyler_Durden]

Sorry there was a mistake in the code...

Please Login or Register  to view this content.

[NBVC]

Does this work:

Please Login or Register  to view this content.

[Tyler_Durden]

You're awesome thats so much!!