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

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

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

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



On Sat, 02 Aug 2003 20:35:08 -0400, Jon Peltier
wrote:

No vba needed, just a few "simple" worksheet formulas.

1. If you know how far down the data goes, use this array formula:

{=AVERAGE(IF(F10:F33=G5,E10:E33))}

Don't type the curly braces: hold down Ctrl+Shift while typing Enter,
and Excel will put them there for you.

I put the target value (e.g., 24) into cell G5, and the data goes from
row 10 to row 33.

If you don't know how far the data extends, you can use OFFSET to
capture it all. This refers to the data in column F from F10 downwards
as far as it extends, as long as there are no blank rows and as long as
there is no numerical data above F10:

OFFSET(F10,0,0,COUNT(F:F),1)

Look up OFFSET in the help files. We'll work it into the above formula:

{=AVERAGE(IF(OFFSET(F10,0,0,COUNT(F:F),1)=G5,OFFS ET(F10,0,-1,COUNT(F:F),1)))}

2. This array formula tells you the last row in which the value in
column F is equal to your target value:

{=MAX(ROW(F10:F33)*(F10:F33=G5))}

This formula then captures the value next to it in column E:

=OFFSET(E1,MAX(ROW(F10:F33)*(F10:F33=G5))-1,0)

Note: Solutions to both of these will fail if none of the cells have the
sought value.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

eggsell wrote:
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

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

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


--

HTH

Bob Phillips

"eggsell" wrote in message
...
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



On Sat, 02 Aug 2003 20:35:08 -0400, Jon Peltier
wrote:

No vba needed, just a few "simple" worksheet formulas.

1. If you know how far down the data goes, use this array formula:

{=AVERAGE(IF(F10:F33=G5,E10:E33))}

Don't type the curly braces: hold down Ctrl+Shift while typing Enter,
and Excel will put them there for you.

I put the target value (e.g., 24) into cell G5, and the data goes from
row 10 to row 33.

If you don't know how far the data extends, you can use OFFSET to
capture it all. This refers to the data in column F from F10 downwards
as far as it extends, as long as there are no blank rows and as long as
there is no numerical data above F10:

OFFSET(F10,0,0,COUNT(F:F),1)

Look up OFFSET in the help files. We'll work it into the above formula:


{=AVERAGE(IF(OFFSET(F10,0,0,COUNT(F:F),1)=G5,OFFS ET(F10,0,-1,COUNT(F:F),1))
)}

2. This array formula tells you the last row in which the value in
column F is equal to your target value:

{=MAX(ROW(F10:F33)*(F10:F33=G5))}

This formula then captures the value next to it in column E:

=OFFSET(E1,MAX(ROW(F10:F33)*(F10:F33=G5))-1,0)

Note: Solutions to both of these will fail if none of the cells have the
sought value.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

eggsell wrote:
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

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

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 Peltier" wrote in message
...
Bob -

Thanks for bailing me out.

Eggs -

I like to offer non-vba solutions because often the posters don't
realize things can be done with worksheet formulas. Bob shows how to
use both techniques.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Bob Phillips wrote:
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


--

HTH

Bob Phillips

"eggsell" wrote in message
...

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



On Sat, 02 Aug 2003 20:35:08 -0400, Jon Peltier
wrote:


No vba needed, just a few "simple" worksheet formulas.

1. If you know how far down the data goes, use this array formula:

{=AVERAGE(IF(F10:F33=G5,E10:E33))}

Don't type the curly braces: hold down Ctrl+Shift while typing Enter,
and Excel will put them there for you.

I put the target value (e.g., 24) into cell G5, and the data goes from
row 10 to row 33.

If you don't know how far the data extends, you can use OFFSET to
capture it all. This refers to the data in column F from F10 downwards
as far as it extends, as long as there are no blank rows and as long as
there is no numerical data above F10:

OFFSET(F10,0,0,COUNT(F:F),1)

Look up OFFSET in the help files. We'll work it into the above
formula:



{=AVERAGE(IF(OFFSET(F10,0,0,COUNT(F:F),1)=G5,OFF SET(F10,0,-1,COUNT(F:F),1)
)

)}

2. This array formula tells you the last row in which the value in
column F is equal to your target value:

{=MAX(ROW(F10:F33)*(F10:F33=G5))}

This formula then captures the value next to it in column E:

=OFFSET(E1,MAX(ROW(F10:F33)*(F10:F33=G5))-1,0)

Note: Solutions to both of these will fail if none of the cells have
the
sought value.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

eggsell wrote:

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

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

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





On Sun, 03 Aug 2003 12:26:06 -0400, Jon Peltier
wrote:

Bob -

Thanks for bailing me out.

Eggs -

I like to offer non-vba solutions because often the posters don't
realize things can be done with worksheet formulas. Bob shows how to
use both techniques.

- Jon
-------

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

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
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

eggsell wrote:
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





On Sun, 03 Aug 2003 12:26:06 -0400, Jon Peltier
wrote:


Bob -

Thanks for bailing me out.

Eggs -

I like to offer non-vba solutions because often the posters don't
realize things can be done with worksheet formulas. Bob shows how to
use both techniques.

- Jon
-------

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

Jon

Thank you so much, it worked great.

Take Care



On Mon, 04 Aug 2003 14:58:43 -0400, Jon Peltier
wrote:

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,RO W(F10:F33)*(F10:F33=G5)))-1,0)}

- Jon
-------

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

and it's so obvious<vbg.

One to add to the UDF collection I think.

Bob

"eggsell" wrote in message
...
Jon

Thank you so much, it worked great.

Take Care



On Mon, 04 Aug 2003 14:58:43 -0400, Jon Peltier
wrote:

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,RO W(F10:F33)*(F10:F33=G5)))-
1,0)}

- Jon
-------