Lookup with Average?

Domroy

Board Regular
Joined
Mar 8, 2018
Messages
108
New formula question - same sheet:

I have a file with several tabs. On my "Dashboard" tab, I have a listing of assets with a unique identifier in column A. In column F, I want to put a formula that will go to the "Hour Meter Reading" tab, look at the array (where there are multiple listings of each asset number for each time the meter was checked on the part). I want it to look in the array (column A is the asset ID, and column F is the Counter reading), and return the average of service hours since the last service, which is listed on the "Plans" tab under the column header "Changed On" (where column A has the asset number on this tab too).

Is that possible?
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
702
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi there,

The easiest way I know, would be to lookup the latest service on "Plans" and have this stored in a new column on "Hour Meter Reading". Assuming here the last service is not historized.
Then on the dashboard you could be using AVERAGEIFS. Or when that function is not available, a SUMIFS and COUNTIFS combo, matching your criteria described. Having them in a single cell like SUMIFS / COUNTIFS to return the average.
 

Domroy

Board Regular
Joined
Mar 8, 2018
Messages
108
What does "historized" mean?

I'm not quite wrapping my brain around your recommendation. Can you give me an example?
 

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
702
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi again,

Historized: that you keep each instance in "plans", in other words you would have multiple records for the same ID.
Depending on your version that might be - not an issue, but more challenging - harder to lookup the correct row. It might be a good idea to update your profile so it includes your office version. Then most of the times people helping out know what kind of formula they can provide that works for you.

If you can share some sanitized data, preferably by using the XL2BB, the more chance you have to receive an answer with a solution.
 

Domroy

Board Regular
Joined
Mar 8, 2018
Messages
108
I can't get it to Download and install. I'll have to troubleshoot. Any other places deemed safe to post my spreadsheet?
 

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
702
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hmm, it is not adviced to share files as mentioned in the forum rules as you know.
Maybe paste in the post text. Not ideal, but it can work. A small sample that showcases the issue at hand is enough.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,206
Messages
5,600,317
Members
414,376
Latest member
NickYOW

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top