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