Hey Guys, I have what seems a simple issue a...

expressions
(CCMET Metrotesting) #1

Hey Guys,

I have what seems a simple issue about making an average number but can’t seem to solve it. I have an app that collects test results and I have 20 numerical fields that will get these test results. Sometimes there can be 1 test sometimes there can be 5, 20 etc… I have tried using the average expression how ever it adds all the values then divides it by 20 automatically so if I only have 5 tests it will still get divided by 20.

I tried COUNT() to count the numeric fields but it gives me an answer of 20 even if all the fields are “empty/not used”. I think this is because the default of number is 0?

Is there a simple way of solving this?

(Reza Raoofi) #2

Try this expression: AVERAGE(SELECT(Table[numeric field],ISNOTBLANK([numeric field])))

I haven’t tested it myself, but I think it should work.

(CCMET Metrotesting) #3

@RezaRaoofi

Hello Reza, Thanks for your reply. I have a question regarding the select… because for select I know you can run it on a different table such as Table[numeric field] but how will I be able to run the formual on the record itself? For example I have [Test1][Test2][Test3][Average]. Do I AVERAGE(SELECT(([Test1],[Test2],[Test3]),ISNOTBLANK(([Test1],[Test2],[Test3]))))? I am sure my formula is wrong though.

(CCMET Metrotesting) #4

I tried the formula below but I was getting an error “LIST has elements of mismatched types” AVERAGE(SELECT(({[Gauge Density 1 (kg/m3)],

[Gauge Density 2 (kg/m3)], [Gauge Density 3 (kg/m3)], [Gauge Density 4 (kg/m3)], [Gauge Density 5 (kg/m3)], [Gauge Density 6 (kg/m3)], [Gauge Density 7 (kg/m3)], [Gauge Density 8 (kg/m3)], [Gauge Density 9 (kg/m3)], [Gauge Density 10 (kg/m3)], [Gauge Density 11 (kg/m3)], [Gauge Density 12 (kg/m3)], [Gauge Density 13 (kg/m3)], [Gauge Density 14 (kg/m3)], [Gauge Density 15 (kg/m3)], [Gauge Density 16 (kg/m3)], [Gauge Density 17 (kg/m3)], [Gauge Density 18 (kg/m3)], [Gauge Density 19 (kg/m3)], [Gauge Density 20 (kg/m3)]}), ISNOTBLANK({[Gauge Density 1 (kg/m3)],

[Gauge Density 2 (kg/m3)], [Gauge Density 3 (kg/m3)], [Gauge Density 4 (kg/m3)], [Gauge Density 5 (kg/m3)], [Gauge Density 6 (kg/m3)], [Gauge Density 7 (kg/m3)], [Gauge Density 8 (kg/m3)], [Gauge Density 9 (kg/m3)], [Gauge Density 10 (kg/m3)], [Gauge Density 11 (kg/m3)], [Gauge Density 12 (kg/m3)], [Gauge Density 13 (kg/m3)], [Gauge Density 14 (kg/m3)], [Gauge Density 15 (kg/m3)], [Gauge Density 16 (kg/m3)], [Gauge Density 17 (kg/m3)], [Gauge Density 18 (kg/m3)], [Gauge Density 19 (kg/m3)], [Gauge Density 20 (kg/m3)]})))