Forum Discussion

Copper Contributor

Oct 05, 2023

Trying to find average and getting #D/0 error

I have been trying to find the average of a column and I keep getting this error but when I try on the columns without dollar signs it works, I have tried to change it from the dollar sign but it won...

excel

Formulas and Functions

PeterBartholomew1

Silver Contributor

Oct 05, 2023

Sven_Tholstrom

If you keep on needing to import dollar values as text and use 365, you could write a Lambda function to perform the calculation you need on the text values.

AverageTextλ
= LAMBDA(text,
    LET(
      convertedToValue, VALUE(TEXTAFTER(text, "$")),
      averageOfValues,  AVERAGE(convertedToValue),
      reconvertToText,  TEXT(averageOfValues, "$0"),
      reconvertToText
    )
  )