need help doing a vlookup and average

S

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

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?
 
J

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?
 
S

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

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
 
J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top