Solved

How to use VLookUp or any other function to retrieve average values of cells?

Posted on 2013-12-18
8
371 Views
Last Modified: 2013-12-18
Hello experts,

You can use the VLOOKUP function to search the first column of a range of cells, and then return a value from any cell on the same row of the range.

Is there anyway I can return all cells that meet my search criteria and average them, rather than only getting the first match.
0
Comment
Question by:Mehawitchi
8 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39726513
You can use MATCH to get the row number.

From that you can use the OFFSET function to get a number of columns in that particular row and then you can average it.
0
 

Author Comment

by:Mehawitchi
ID: 39726518
Thanks  ssaqibh

I'm not quite sure how to build this into a formula

Can you show me an example please
0
 
LVL 49

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 50 total points
ID: 39726523
Hi,

Why not use

=SUMIF(D1:D5,1,E1:E5)/COUNTIF(D1:D5,1)

Open in new window

Regards
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 100 total points
ID: 39726525
Here is a sample
lookup-Average.xlsx
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 350 total points
ID: 39726555
If you are using excel 2007 or later then AVERAGIF function allows you to average data based on a criterion, e.g. to average all cells in column B when column A = "x"

=AVERAGEIF(A:A,"x","B:B)

regards, barry
0
 

Author Closing Comment

by:Mehawitchi
ID: 39726594
Barry's solution is exactly what I need.
Thank you all
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39726596
I clearly misunderstood your requirements. You should have awarded all points to Barry.
0
 

Author Comment

by:Mehawitchi
ID: 39726618
No worries ssaqibh. You made a good attempt after all
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

778 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question