View Full Version : Solved: Averaging in macros
constamj
11-24-2008, 09:30 AM
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
11-24-2008, 09:53 AM
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
11-24-2008, 10:12 AM
.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
11-24-2008, 10:57 AM
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
11-24-2008, 01:27 PM
I went to class and thought about it for a bit and realized what I had missed.
constamj
11-24-2008, 01:33 PM
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
11-24-2008, 01:38 PM
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
11-24-2008, 01:52 PM
Sorry about that but it works now thanks!!!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.