Forum Discussion

PriyaM14's avatar
PriyaM14
Copper Contributor
Jun 15, 2023

Calculating average from different workbook

how do you find the average of a collumn from another workbook (BI) by looking up a condition. E.g. lookup cell D5 in dummy1 workbook in dummy2 workbook by looking at column B and returning the avera...
Formulas and Functions
NikolinoDE's avatar
NikolinoDE
Gold Contributor
Jun 16, 2023

PriyaM14 

To find the average of a column from another workbook based on a condition, you can use the combination of the VLOOKUP function and the AVERAGEIF function.

Here is the step-by-step process:

  1. Open both the "dummy1" workbook and the "dummy2" workbook in Excel.
  2. In the "dummy2" workbook, select a cell where you want to display the average.
  3. Enter the following formula:

=AVERAGEIF('[dummy1.xlsx]Sheet1'!$B:$B, D5, '[dummy1.xlsx]Sheet1'!$BI:$BI)

This formula uses the AVERAGEIF function to calculate the average of values in column BI in the "dummy1" workbook's Sheet1, based on the condition that values in column B match the value in cell D5.

Note: Replace '[dummy1.xlsx]' with the actual file name of the "dummy1" workbook, and adjust the sheet name (Sheet1) if necessary.

  1. Press Enter to get the average value.

The formula will fetch the values from the "dummy1" workbook, apply the condition, and calculate the average accordingly. Make sure both workbooks are open, and the file paths and sheet names are accurate in the formula.

Keep in mind that the referenced workbook should remain open for the formula to work. If you close the "dummy1" workbook or change its location, you may need to update the file path in the formula accordingly.

Resources