Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need help doing a vlookup and average
Hi,
Right now I have two sheets, sheet 1 has a list of dates in column A (each date only listed once). Sheet 2 has individual purchases, with the dates in column A and the price in column B. Based on the date (listed in one cell) in sheet 1 i want to find the average price of all the purchases on that date in sheet 2 (listed in many cells). Right now, in sheet 1, cell B3, my formula is : =AVERAGE(VLOOKUP($A3,'Bond 1 Analysis (3)'!$A$3:$B$11357,2,FALSE)) I am getting a number (not "N/A", etc), but it is different than the average I got for the date when I did it manually to check. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need help doing a vlookup and average
On Mar 18, 11:05*am, SK08 wrote:
Hi, Right now I have two sheets, sheet 1 has a list of dates in column A (each date only listed once). Sheet 2 has individual purchases, with the dates in column A and the price in column B. Based on the date (listed in one cell) in sheet 1 i want to find the average price of all the purchases on that date in sheet 2 (listed in many cells). Right now, in sheet 1, cell B3, my formula is : =AVERAGE(VLOOKUP($A3,'Bond 1 Analysis (3)'!$A$3:$B$11357,2,FALSE)) I am getting a number (not "N/A", etc), but it is different than the average I got for the date when I did it manually to check. Clarification: Right now it is only returning the first purchase price for the date I'm looking up.. How can I get it to continue to search for that date and average all prices together? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need help doing a vlookup and average
would this (CTRL+SHIFT+ENTER this formula):
=AVERAGE(IF($A3='Bond 1 Analysis (3)'!$A$3:$A$11357,'Bond 1 Analysis (3)'!$B$3:$B$11357,)) help? On 18 Mar, 16:09, SK08 wrote: On Mar 18, 11:05*am, SK08 wrote: Hi, Right now I have two sheets, sheet 1 has a list of dates in column A (each date only listed once). Sheet 2 has individual purchases, with the dates in column A and the price in column B. Based on the date (listed in one cell) in sheet 1 i want to find the average price of all the purchases on that date in sheet 2 (listed in many cells). Right now, in sheet 1, cell B3, my formula is : =AVERAGE(VLOOKUP($A3,'Bond 1 Analysis (3)'!$A$3:$B$11357,2,FALSE)) I am getting a number (not "N/A", etc), but it is different than the average I got for the date when I did it manually to check. Clarification: Right now it is only returning the first purchase price for the date I'm looking up.. How can I get it to continue to search for that date and average all prices together? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need help doing a vlookup and average
On Mar 18, 11:23*am, Jarek Kujawa wrote:
would this (CTRL+SHIFT+ENTER this formula): =AVERAGE(IF($A3='Bond 1 Analysis (3)'!$A$3:$A$11357,'Bond 1 Analysis (3)'!$B$3:$B$11357,)) help? On 18 Mar, 16:09, SK08 wrote: On Mar 18, 11:05*am, SK08 wrote: Hi, Right now I have two sheets, sheet 1 has a list of dates in column A (each date only listed once). Sheet 2 has individual purchases, with the dates in column A and the price in column B. Based on the date (listed in one cell) in sheet 1 i want to find the average price of all the purchases on that date in sheet 2 (listed in many cells). Right now, in sheet 1, cell B3, my formula is : =AVERAGE(VLOOKUP($A3,'Bond 1 Analysis (3)'!$A$3:$B$11357,2,FALSE)) I am getting a number (not "N/A", etc), but it is different than the average I got for the date when I did it manually to check. Clarification: Right now it is only returning the first purchase price for the date I'm looking up.. How can I get it to continue to search for that date and average all prices together?- Hide quoted text - - Show quoted text - Yes! That did work, thank you... I do have excel 2007, though.. Any idea of how to do this w/o using array? As you can see, I'm dealing with over 11k rows.... |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need help doing a vlookup and average
i'd try SUMPRODUCT/COUNTIF
SUMPRODUCT to calculate the sum for a given date COUNTIF to count the number of given date's occurances On 18 Mar, 16:30, SK08 wrote: On Mar 18, 11:23*am, Jarek Kujawa wrote: would this (CTRL+SHIFT+ENTER this formula): =AVERAGE(IF($A3='Bond 1 Analysis (3)'!$A$3:$A$11357,'Bond 1 Analysis (3)'!$B$3:$B$11357,)) help? On 18 Mar, 16:09, SK08 wrote: On Mar 18, 11:05*am, SK08 wrote: Hi, Right now I have two sheets, sheet 1 has a list of dates in column A (each date only listed once). Sheet 2 has individual purchases, with the dates in column A and the price in column B. Based on the date (listed in one cell) in sheet 1 i want to find the average price of all the purchases on that date in sheet 2 (listed in many cells). Right now, in sheet 1, cell B3, my formula is : =AVERAGE(VLOOKUP($A3,'Bond 1 Analysis (3)'!$A$3:$B$11357,2,FALSE)) I am getting a number (not "N/A", etc), but it is different than the average I got for the date when I did it manually to check. Clarification: Right now it is only returning the first purchase price for the date I'm looking up.. How can I get it to continue to search for that date and average all prices together?- Hide quoted text - - Show quoted text - Yes! That did work, thank you... I do have excel 2007, though.. Any idea of how to do this w/o using array? As you can see, I'm dealing with over 11k rows....- Ukryj cytowany tekst - - Poka¿ cytowany tekst - |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need help doing a vlookup and average
also you might want to take a look he
http://www.cpearson.com/Excel/lists.htm#Functions On 18 Mar, 16:37, Jarek Kujawa wrote: i'd try SUMPRODUCT/COUNTIF SUMPRODUCT to calculate the sum for a given date COUNTIF to count the number of given date's occurances On 18 Mar, 16:30, SK08 wrote: On Mar 18, 11:23Â*am, Jarek Kujawa wrote: would this (CTRL+SHIFT+ENTER this formula): =AVERAGE(IF($A3='Bond 1 Analysis (3)'!$A$3:$A$11357,'Bond 1 Analysis (3)'!$B$3:$B$11357,)) help? On 18 Mar, 16:09, SK08 wrote: On Mar 18, 11:05Â*am, SK08 wrote: Hi, Right now I have two sheets, sheet 1 has a list of dates in column A (each date only listed once). Sheet 2 has individual purchases, with the dates in column A and the price in column B. Based on the date (listed in one cell) in sheet 1 i want to find the average price of all the purchases on that date in sheet 2 (listed in many cells). Right now, in sheet 1, cell B3, my formula is : =AVERAGE(VLOOKUP($A3,'Bond 1 Analysis (3)'!$A$3:$B$11357,2,FALSE)) I am getting a number (not "N/A", etc), but it is different than the average I got for the date when I did it manually to check. Clarification: Right now it is only returning the first purchase price for the date I'm looking up.. How can I get it to continue to search for that date and average all prices together?- Hide quoted text - - Show quoted text - Yes! That did work, thank you... I do have excel 2007, though.. Any idea of how to do this w/o using array? As you can see, I'm dealing with over 11k rows....- Ukryj cytowany tekst - - Poka¿ cytowany tekst -- Ukryj cytowany tekst - - Pokaż cytowany tekst - |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need help doing a vlookup and average
i.e.
=SUMPRODUCT(($A3='Bond 1 Analysis (3)'!$A$3:$A$11357)*('Bond 1 Analysis (3)'!$B$3:$B$11357)))/COUNTIF('Bond 1 Analysis (3)'!$A$3:$A $11357,$A3) hope it will work, cannot test it now On 18 Mar, 16:37, Jarek Kujawa wrote: i'd try SUMPRODUCT/COUNTIF SUMPRODUCT to calculate the sum for a given date COUNTIF to count the number of given date's occurances On 18 Mar, 16:30, SK08 wrote: On Mar 18, 11:23Â*am, Jarek Kujawa wrote: would this (CTRL+SHIFT+ENTER this formula): =AVERAGE(IF($A3='Bond 1 Analysis (3)'!$A$3:$A$11357,'Bond 1 Analysis (3)'!$B$3:$B$11357,)) help? On 18 Mar, 16:09, SK08 wrote: On Mar 18, 11:05Â*am, SK08 wrote: Hi, Right now I have two sheets, sheet 1 has a list of dates in column A (each date only listed once). Sheet 2 has individual purchases, with the dates in column A and the price in column B. Based on the date (listed in one cell) in sheet 1 i want to find the average price of all the purchases on that date in sheet 2 (listed in many cells). Right now, in sheet 1, cell B3, my formula is : =AVERAGE(VLOOKUP($A3,'Bond 1 Analysis (3)'!$A$3:$B$11357,2,FALSE)) I am getting a number (not "N/A", etc), but it is different than the average I got for the date when I did it manually to check. Clarification: Right now it is only returning the first purchase price for the date I'm looking up.. How can I get it to continue to search for that date and average all prices together?- Hide quoted text - - Show quoted text - Yes! That did work, thank you... I do have excel 2007, though.. Any idea of how to do this w/o using array? As you can see, I'm dealing with over 11k rows....- Ukryj cytowany tekst - - Poka¿ cytowany tekst -- Ukryj cytowany tekst - - Pokaż cytowany tekst - |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need help doing a vlookup and average
Hi,
In Excel 2007, you can use AVERAGEIF() formula. Please read on it in the Help menu -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "SK08" wrote in message ... Hi, Right now I have two sheets, sheet 1 has a list of dates in column A (each date only listed once). Sheet 2 has individual purchases, with the dates in column A and the price in column B. Based on the date (listed in one cell) in sheet 1 i want to find the average price of all the purchases on that date in sheet 2 (listed in many cells). Right now, in sheet 1, cell B3, my formula is : =AVERAGE(VLOOKUP($A3,'Bond 1 Analysis (3)'!$A$3:$B$11357,2,FALSE)) I am getting a number (not "N/A", etc), but it is different than the average I got for the date when I did it manually to check. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average of Vlookup data | Excel Worksheet Functions | |||
vlookup then average 3 above and below | Excel Worksheet Functions | |||
Combining Average, Offset & Vlookup | Excel Worksheet Functions | |||
vlookup average() | Excel Worksheet Functions | |||
Using VLookup, Displacement, SumIF to total and average | Excel Discussion (Misc queries) |