Excluding Cells From Avg Cals Due to Info in different col
 

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

Excluding Cells From Avg Cals Due to Info in different col
 

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.

--
Biff
Microsoft Excel MVP


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

Excluding Cells From Avg Cals Due to Info in different col
 

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
---------------
Jacob Skaria


"redstar_" wrote:

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

Excluding Cells From Avg Cals Due to Info in different col
 

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


"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"redstar_" wrote:

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

Excluding Cells From Avg Cals Due to Info in different col
 

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

If this post helps click Yes
---------------
Jacob Skaria


"redstar_" wrote:

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


"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"redstar_" wrote:

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

Excluding Cells From Avg Cals Due to Info in different col
 

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.

"Jacob Skaria" wrote:

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

If this post helps click Yes
---------------
Jacob Skaria


"redstar_" wrote:

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


"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"redstar_" wrote:

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

Excluding Cells From Avg Cals Due to Info in different col
 

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
---------------
Jacob Skaria


"redstar_" wrote:

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.

"Jacob Skaria" wrote:

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

If this post helps click Yes
---------------
Jacob Skaria


"redstar_" wrote:

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


"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"redstar_" wrote:

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