Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 |
thank you very much!
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 |
thank you very much!