Excluding Cells From Avg Cals Due to Info in different col

R

redstar_

I have an investment spreadsheet that shows the qunaity I bought or sold, buy
date, buy price, sell date, and sell price. I am trying to calcualte the
average buy price for the investments. Unfortunatley when you sell it
includes a buy price as well. I need to exclude these cells from the
average. Below is the info.

Col C Col D Col E Col F Col G
Quantity Buy Date Buy Price Sell Date Sell Price
18.755 5/21/2008 36.835 0.000
0.009 5/21/2008 36.835 12/31/2008 24.846
0.010 5/21/2008 36.835 3/31/2009 24.329
0.592 7/3/2008 33.761 0.000

I want to get the average of Col E but exclude the two items that have sell
dates associated with them. I do not know VBA. Thanks for your help
 
T

T. Valko

Try this array formula** :

=AVERAGE(IF(F2:F5="",IF(E2:E5<>"",E2:E5)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
 
J

Jacob Skaria

Please note that this is an array formula. You create array formulas in the
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula>}"

=AVERAGE(IF(F1:F10="",IF(ISNUMBER(E1:E10),E1:E10)))

If this post helps click Yes
 
R

redstar_

Thanks that worked perfectly. Here is the second part

ColA Col B Col C Col D Col E Col F
Investment Quantity Buy Date Buy Price Sell Date Sell Price
American 18.755 5/21/2008 36.835 0.000
American 0.009 5/21/2008 36.835 12/31/200824.846
American 0.010 5/21/2008 36.835 3/31/2009 24.329
American 0.592 7/3/2008 33.761 0.000
British 0.185 7/3/2008 33.761 0.000
Canadian 0.592 7/3/2008 33.761 0.000

Same thing as before but I only want to include Col A investments that are
American. Before I was figuring the total number of shares bought by
investment group using a sumif statement. In this case would I use a
AverageIF statement. If so would that go before the function you sent
before. Thanks
 
J

Jacob Skaria

Try (array entered)
=AVERAGE(IF((A1:A10="American")*(E1:E10=""),D1:D10))

If this post helps click Yes
 
R

redstar_

That worked. Thanks for the help.

I am trying to understand the logic of that function. If I read it right it
says:

Look at column E (Sell Date) and if it is blank take the average of the
number in column D (Buy Price) next to a blank space and avarge them
together.

What I don't understand is the use of the * what is that doing.
 
J

Jacob Skaria

You can try..
=AVERAGE(IF(A1:A10="American",IF(E1:E10="",D1:D10)))

'Multiple conditions are combined within one IF condition
=AVERAGE(IF((A1:A10="American")*(E1:E10=""),D1:D10))

(A1:A10="American")*(E1:E10="") equates to

TRUE * TRUE returns 1
FALSE * TRUE returns 0
TRUE * FALSE returns 0

So the resultant array will have values of 1 and 0; 1 corresponding to both
the conditions being true.. SUMPRODUCT() this array with the array D1:D10
will return the multiply these two arrays and return the SUM.

If this post helps click Yes
 
Top