Averages By V Look Up

  • Thread starter Thread starter Paul
  • Start date Start date
P

Paul

I am trying to insert the average of D into B by count of
A. Presuming that C & D are my look up data.

A B C D
1 3000 1 6000
1 3000 2 5000
2 5000 3 6000
3 2000
3 2000
3 2000

I want to be able to do this by v look up if possible.
Column B is how I would like it to look. Column C is the
reference for A. Thanks in advance.
 
Below assumes that the Column A 1 starts at A2

=(VLOOKUP(A2,$c$2:$d$4,2,FALSE))/(COUNTIF($A$2:$A$7,A2))
=(VLOOKUP(A3,$c$2:$d$4,2,FALSE))/(COUNTIF($A$2:$A$7,A3))

Bill
 
Good morning Paul -

Yo may want to try this formula:

=VLOOKUP(A1,$C$1:$D$3,2)/COUNTIF($A$1:$A$6,A1)

If you put it in B1 - Enter it and then fill down it should do the trick for you.

Let me know if you need further information concerning this issue.

Thanks,
Jon Barchenger
--------------------
**Content-Class: urn:content-classes:message
**From: "Paul" <[email protected]>
**Sender: "Paul" <[email protected]>
**Subject: Averages By V Look Up
**Date: Tue, 9 Dec 2003 08:27:53 -0800
**Lines: 14
**Message-ID: <[email protected]>
**MIME-Version: 1.0
**Content-Type: text/plain;
** charset="iso-8859-1"
**Content-Transfer-Encoding: 7bit
**X-Newsreader: Microsoft CDO for Windows 2000
**X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
**Thread-Index: AcO+cWU9Xw6a/d8/Taaj7XpZN51q8Q==
**Newsgroups: microsoft.public.excel.worksheet.functions
**Path: cpmsftngxa07.phx.gbl
**Xref: cpmsftngxa07.phx.gbl microsoft.public.excel.worksheet.functions:175851
**NNTP-Posting-Host: tk2msftngxa09.phx.gbl 10.40.1.161
**X-Tomcat-NG: microsoft.public.excel.worksheet.functions
**
**I am trying to insert the average of D into B by count of
**A. Presuming that C & D are my look up data.
**
**A B C D
**1 3000 1 6000
**1 3000 2 5000
**2 5000 3 6000
**3 2000
**3 2000
**3 2000
**
**I want to be able to do this by v look up if possible.
**Column B is how I would like it to look. Column C is the
**reference for A. Thanks in advance.
**
 
Back
Top