You are here:

Excel/Formula to extract average.

Advertisement


Question
QUESTION: Sir,

In sheet2 i have a list of persons with their classes in column B and their average marks obtained in each individual classes in column C. In sheet1 column B i have list of persons with their classes. How i can extract average marks of every classes for each persons in sheet1 column C?

For example:
sheet2 have this list:
Names.          Average
With class      marks
James
Class I          245
Class 2         320
Class 3         430
Class 4         285
Class 5.       350
Class 6 and so on upto class 12

Thompson
Class 1.        430
Class 2         270
Class 3 and so on up to class 10

Jack
Class 1.     410
Class 2      385
Class 3 and so on up to class 12


Sheet1 column B have list like this:

Jack
Class 2
Class 7
Class 8
Class 9
Class 12

James
Class 1
Class 2
Class 4
Class 5
Class 6
Class 12

I have long list of persons with their classes in both the sheets. How to extract average marks in sheet1 column C?

ANSWER: Nabam,

Based on the information provided,

Assume in Sheet1

B2:  James
B3:  Class 1
C3:  =VLOOKUP($B3,OFFSET(Sheet2!$B$1,MATCH($B$2,Sheet2!B:B,0)-1,0,13,2),2,FALSE)
and drag fill down to C14

B16:  Jack
B17:  Class 1
C17:  =VLOOKUP($B17,OFFSET(Sheet2!$B$1,MATCH($B$16,Sheet2!B:B,0)-1,0,13,2),2,FALSE)
and drag fill down to C28

and so forth.

--
Regards,
Tom Ogilvy


---------- FOLLOW-UP ----------

QUESTION: Thanks for reply. It would be a daunting task to enter formula for every persons as list are very long. Can VBA should be used instead? There is a serial No. in culumn A for every person in both the sheet which could be used to identify (Differentiate name) persons with their classes. such as:

1   James
   Class 1
   Class 2

2. Thompson
   Class 1
   Class 2

3. Jack
  Class 1
  Class 2

The task shall be to identify changes in serial No. then match the name on corresponding row in column B. But then serial No. for Jack in sheet1 may be not same in sheet2. In sheet1 Jack may be at serial No.3 but in sheet2 his name may be at Serial No. 145. Can you provide code to extract average marks?

Thanks.

ANSWER: Nabam,


You can try this, It assumes that the first name starts in B2 or below and there are only names and class names in column B from B2 down.  

Sub ABC()
Dim sh As Worksheet
Dim r As Range, r1 As Range, ar As Range, cell As Range
Dim s As String, s1 As String
s = "=VLOOKUP(XXX,OFFSET(Sheet2!$B$1,MATCH(YYY,Sheet2!B:B,0)-1,0,13,2),2,FALSE)"
Set sh = Worksheets("sheet1")
Set r = sh.Range("B2", sh.Cells(sh.Rows.Count, "B").End(xlUp))
Set r1 = r.SpecialCells(xlConstants)
For Each ar In r1.Areas
 Set cell = ar(1)
 s1 = s
 s1 = Replace(s1, "XXX", cell.Offset(1, 0).Address(0, 1, xlA1))
 s1 = Replace(s1, "YYY", cell.Offset(0, 0).Address(1, 1, xlA1))
 cell.Offset(1, 1).Resize(12, 1).Formula = s1
Next
End Sub

--
Regards,
Tom Ogilvy


---------- FOLLOW-UP ----------

QUESTION: Sir, not all person read upto class 12 as in the case of Thompson in my original post and i may not enter all classes of a person in sheet1. For example in case of Jack:
Class 2
Class 7
Class 8
Class 9
Class 12

here i left class 1, 3, 4,, 5, 6, 10 and 11.

Thanks again.

Answer
Nabam

so I adjusted the code to account for a variable number of classes.


Sub ABC()
Dim sh As Worksheet
Dim r As Range, r1 As Range, ar As Range, cell As Range
Dim s As String, s1 As String
s = "=VLOOKUP(XXX,OFFSET(Sheet2!$B$1,MATCH(YYY,Sheet2!B:B,0)-1,0,13,2),2,FALSE)"
Set sh = Worksheets("sheet1")
Set r = sh.Range("B2", sh.Cells(sh.Rows.Count, "B").End(xlUp))
Set r1 = r.SpecialCells(xlConstants)
For Each ar In r1.Areas
 Set cell = ar(1)
 s1 = s
 s1 = Replace(s1, "XXX", cell.Offset(1, 0).Address(0, 1, xlA1))
 s1 = Replace(s1, "YYY", cell.Offset(0, 0).Address(1, 1, xlA1))
 cell.Offset(1, 1).Resize(ar.rows.count - 1, 1).Formula = s1
Next
End Sub

--
Regards,
Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.