Solved: Averaging in macros

[constamj]

What I am trying to do is, with a macro, take all the cells that are named "A" in column A and then take the corresponding numbers that are in column E and average those numbers. Then take the all the cells that are named "B" and do the same thing. Then put the average of "A","B", ect. in column F some where. I can do this in excel, but it will take to long because i have 1000s of cells to average out with around 40 letters so I want an easier way to do this.

Thanks

[Bob Phillips]

Off the top



Dim LastRow As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("F1").Value = .Evaluate("AVERAGE(IF(A1:A" & LastRow & "={""A"",""B"",""E""},E1:E" & LastRow & "))")

End With

[nst1107]

.Range("F1").Value = .Evaluate("AVERAGE(IF(A1:A" & LastRow & "={""A"",""B"",""E""},E1:E" & LastRow & "))")


This returns the same as

ActiveSheet.Range("F1").Value = ActiveSheet.Evaluate("AVERAGE(E1:E" & LastRow & ")")

Which is just the average of column E.

I doubt this is what you are looking for, constamj. Can you clarify what you are looking for?

Edit: Unless, of course, column A has letters you want to skip. In which case, my bad for this post.

[Bob Phillips]

This returns the same as

ActiveSheet.Range("F1").Value = ActiveSheet.Evaluate("AVERAGE(E1:E" & LastRow & ")")

Which is just the average of column E.

I doubt this is what you are looking for, constamj. Can you clarify what you are looking for?

Edit: Unless, of course, column A has letters you want to skip. In which case, my bad for this post.

You beat me to it, I was just about to post a picture showing you the difference.

[nst1107]

I went to class and thought about it for a bit and realized what I had missed.

[constamj]

I was looking for somthing to give me the average of just "A" then under it gives me the average of just "B" and so on.

[Bob Phillips]

You said you wanted the average of all. For just A, use



.Range("F1").Value = .Evaluate("AVERAGE(IF(A1:A" & LastRow & "=""A"",E1:E" & LastRow & "))")


etc.

[constamj]

Sorry about that but it works now thanks!!!