Skip to main content

Trying to figure out a formula to run a conditional average:


Within the table, each record is a single asset. One field tells me what asset it is (unique ID), one field shows me where its physically located, another shows me the type of asset (may assets, although all unique are the same TYPE, etc.). I’ve got another field that contains a formula that shows me the average use each asset receive within a given time sample - that field/formula work perfectly.


What I’m trying to do is average that use for the assets, but ONLY where the location and type are the same.


Example - Location #3 has 4 assets. 3 are Type 1, the last is Type 2. The use formula is returning values of .25, .50 and .75 for the three Type 1 assets. I’d like this formula to average those 3 values together to return .5, BUT ONLY for Location #3, and only for Type 1 assets. The Type 2 asset should remain blank because its the only Type 2 asset at that location - there is nothing to average it with, etc.

Hi @Mike_Buck and welcome back!


Are you using a linked field to link your locations to your assets or a select field? I think not, but if it is, you can use a rollup field with an average formula:



The record with “location #3” will then show .50 as average.

Hi @Mike_Buck and welcome back!


Are you using a linked field to link your locations to your assets or a select field? I think not, but if it is, you can use a rollup field with an average formula:



The record with “location #3” will then show .50 as average.


I thought about that, but the value I’m needing to average is in this existing table, not another one.


The other issue is that there are say 2000 assets, 500 locations and maybe 70 types… Almost need like an excel averageif where instead of clarifying all the variable I can match up to a cell value if that makes sense?

I thought about that, but the value I’m needing to average is in this existing table, not another one.


The other issue is that there are say 2000 assets, 500 locations and maybe 70 types… Almost need like an excel averageif where instead of clarifying all the variable I can match up to a cell value if that makes sense?



I understand, but getting the locations in a separate table and then linking the assets to them, makes it easy to rollup data like this - which isn’t possible if you stay in 1 table, because there is no option (except the grouping one) to do calculations across multiple records.



Smarter people than me will no doubt have some ideas to tackle this 🙂

Reply


Terms & Conditions