+ Reply to Thread
Results 1 to 5 of 5

from a range to a single value

  1. #1
    Registered User
    Join Date
    01-28-2017
    Location
    Philadelphia, PA
    MS-Off Ver
    Excel 15.13.1
    Posts
    3

    from a range to a single value

    I am downloading data that is currently in the form of arrange(e.g. 55-60). I would like to ask Excel to take that range and find the middle point and replace the range with the middle point value. There is a large amount of data that I want to convert this way. Right now my cells look like this: 0.50 - 0.55

    I want it to look like this: 0.525

    Does anyone know any easy way to do this?

    Best, Maddy

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: from a range to a single value

    You could use this formula:

    =AVERAGE(RIGHT(A11,LEN(A11) -FIND("-",A11)),LEFT(A11,FIND("-",A11)-1))

    assuming your range is in cell A11.

    Hope this helps.

    Pete

  3. #3
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: from a range to a single value

    You could split the data into separate cells...

    If A2 contains 0.50 - 0.55 make sure column B is empty to receive the parsed data.

    Select the data in question
    Goto the Data tab>Text to Columns
    Select: Delimited>Next
    Select: Other>Enter a dash - in the little box
    Click: Finish

    Now, enter this formula in C2:

    =MEDIAN(A2:B2)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Registered User
    Join Date
    01-28-2017
    Location
    Philadelphia, PA
    MS-Off Ver
    Excel 15.13.1
    Posts
    3

    Re: from a range to a single value

    Thanks you guys!!! Really appreciate it! Maddy

  5. #5
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: from a range to a single value

    You're welcome!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 04-06-2016, 07:02 AM
  2. Replies: 6
    Last Post: 02-12-2015, 05:45 AM
  3. Replies: 1
    Last Post: 11-09-2014, 06:04 PM
  4. [SOLVED] dynamic named range not populating combo box list if range = single cell
    By HeyInKy in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-24-2014, 05:27 PM
  5. Count a single range using additional criteria from another range
    By FatFoot in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-27-2013, 08:40 PM
  6. Replies: 5
    Last Post: 07-27-2010, 10:58 AM
  7. Replies: 3
    Last Post: 02-15-2006, 01:10 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1