Average of values of cost per unit

[joanneandrews]

Good day,

I am needing to calculate the average of the column below, but need to exclude the $ amount. 

FY19 Cost per Unit

6.76 ZAR/EA

6.90 ZAR/EA

6.23 ZAR/EA

$ 0.79 /EA

So the answer needs to be 6.63 instead of 5.17.

hope that makes some sort of sense.

thanks in advance 

Joanne

[Anonymous · Accepted solution]

Hello Joanne,

use wildmatch to detect the row with $.

in a straight table this Expression works for me (not sure how to avoid the Division by 100, as keepchar with additionaö character '.' doesnot work).

= if (wildmatch([FY19 Cost per Unit],'*$*')=0,avg(keepchar([FY19 Cost per Unit],'0123456789')/100))

With same Dimension I get

	6.63
6.76 ZAR/EA	6.76
6.90 ZAR/EA	6.9
6.23 ZAR/EA	6.23

[joanneandrews · Accepted solution]

thank you very much!