need help doing a vlookup and average

[SK08]

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.

 

[SK08]

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?

 

[Jarek Kujawa]

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?

 

[SK08]

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?




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

 

[Jarek Kujawa]

i'd try SUMPRODUCT/COUNTIF

SUMPRODUCT to calculate the sum for a given date
COUNTIF to count the number of given date's occurances

 

[Jarek Kujawa]

also you might want to take a look here:

http://www.cpearson.com/Excel/lists.htm#Functions

 

[Jarek Kujawa]

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

 

[Ashish Mathur]

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