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.

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?

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?

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....

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 -

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 -

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 -

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.