Forum Discussion

Sven_Tholstrom's avatar
Sven_Tholstrom
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 wont let me change it from the data I was given that i copied into excel

excel
Formulas and Functions

4 Replies

  • PeterBartholomew1's avatar
    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
    )
  )

     

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Oct 05, 2023

    Sven_Tholstrom I suspect that the amounts with dollar signs are texts, not numbers. Then AVERAGE will  generate the #DIV/0! error.

    Try using Find & Replace (Ctrl-H) to replace $ with nothing. That should turn the values into numbers and AVERAGE will work as intended.

    • SanthoshKunder's avatar
      SanthoshKunder
      Iron Contributor
      Oct 05, 2023
      If you can execute a Find & Replace for "$," it indicates that the "$" symbol wasn't added through formatting, but rather entered manually.. You can replace $ symbol and subsequently adjust the data type to currency from format dialog box in order to preserve the currency formatting.

Resources