vba: Taking average of values in one column based on a corresponding value in another column

  • Thread starter Thread starter eggsell
  • Start date Start date
E

eggsell

Excel XP, Windows XP

I have dynamically changing data in columns E and F that start at row
10 and continue down, I would like to be able to accomplish the
following using vba:

1) Take the average of all the data in E that has a corresponding
value (say, 24) in F and then enter that average in column G.
2) Find the last cell in F that contains 24 and enter its
corresponding value from E in column G.


Any help is greatly appreciated, as I am stuck in the mud when it
comes to vba.
Thanks
 
Hi Jon,

Thanks for the reply. But I do need a vba solution, since I have to
delete the contents of the column E and F later on. Sorry for not
being thorough in describing the problem. The fact is that I need to
do some more operations on the data. I was hoping that if I find a
solution for Averaging problem, I'll be able to come up with the
solution for the rest of them.

Take care
 
Here's some VBA that utilises Jon's averaging formula

Dim crows As Long
Dim sFormula As String
Dim i As Long

cRows = Cells(Rows.Count, "E").End(xlUp).Row

sFormula = "=AVERAGE(IF(R10C6:R" & cRows & "C6=24,R10C5:R" & cRows &
"C5))"
Cells(cRows + 1, "G").FormulaArray = sFormula

For i = cRows To 10 Step -1
If Cells(i, "F").Value = 24 Then
Cells(i, "G").Value = Cells(i, "F").Value
Exit For
End If
Next i
 
Jon,

Amen to that. Too often VBA is resorted to when formulae do the job.

On my post,. I really should have used you MAX formula as well<vbg>.

Regards

Bob
 
Jon:

Although I needed to solution in vba format, your answers proved to be
quite useful. I adapted it into vba and got the part-1 of the
solution working. Unfortunately, I have made a mistake in describing
the part-2 problem.

I needed the FIRST cell that contains the value (24) rather than the
LAST cell. I thought that if I replace MAX function in your formula
with MIN, I should be able to get the first cell with the lowest ROW
number. However, I can't get it working. I always get 0 when I use
MIN in the formula. I also used SMALL, but again, no success.

Any suggestions would be great.

Thanks again
 
MIN returns 0 because the minimum occurs when (F10:F33=G5) is false
(i.e., zero). Try this one:

{=OFFSET(E1,MIN(IF(ROW(F10:F33)*(F10:F33=G5)>0,ROW(F10:F33)*(F10:F33=G5)))-1,0)}

- Jon
 
Back
Top