Forum Discussion

Thaddaeus's avatar
Thaddaeus
Copper Contributor
Nov 24, 2024

Numeric range conversion

As part of survey data, I have an Excel column that is a numeric range for the time each respondent commutes (in minutes): 1-5, 6-10, 11-15, 16-20, 21-25, etc. up to 85-90, 90+. This was a mistake as I ended up needing a single number in order to calculate correlations.

 

Does anyone have a formula for converting a numeric range to a single digit average? Such as 16-20 being converted to 18 and so on? Thank you!!!

BI & Data Analysis
excel
Excel on Mac
Formulas and Functions

3 Replies

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Nov 24, 2024

    To add some more 365 options you could have

    = MAP(numericRange, LAMBDA(r, 
    AVERAGE(VALUE(TEXTSPLIT(r,  "-")))
  ))

    or, using a named Lambda function to simplify the worksheet formula,

    = MAP(numericRange, RangeAverageλ)

RangeAverageλ
= LAMBDA(r,
    AVERAGE(
        VALUE(
            REGEXEXTRACT(r,"\d+", 1)
        )
    )
);

     

Resources