Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy info into empty cells below info, until finds cell with new d | Excel Discussion (Misc queries) | |||
Help excluding some info from counta formula | Excel Discussion (Misc queries) | |||
Spreadsheet cals very slow, I have 2 Gig Ram...? | Excel Discussion (Misc queries) | |||
Link info in one cell to info in several cells in another column (like a database) | Excel Discussion (Misc queries) | |||
Excluding cells | Excel Worksheet Functions |