Lookup with Average?

Domroy

Board Regular
Joined
Mar 8, 2018
Messages
114
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?
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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.
 
Upvote 0
What does "historized" mean?

I'm not quite wrapping my brain around your recommendation. Can you give me an example?
 
Upvote 0
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.
 
Upvote 0
I can't get it to Download and install. I'll have to troubleshoot. Any other places deemed safe to post my spreadsheet?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,344
Members
448,570
Latest member
rik81h

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
Back
Top