• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Get the desired number from a range

adamuce

Member
Dear all,

I hope everyone is well. I am given a price range daily and I wanted to know what formula I can use to get the daily high and low from each range daily.

I have attached a screenshot. The desired output is in yellow

TIA
 

Attachments

Marc L

Excel Ninja
Hi !​
If cell B2 contains the range like '50-100' so the beginner level formula for cell​
  • B3 is =LEFT(B$2,SEARCH("-",B$2)-1)

  • B4 is =RIGHT(B$2,LEN(B$2)-SEARCH("-",B$2))
 

adamuce

Member
Many many thanks! I was using the left and right functions, but then number of digits was changing frequently. Really appreciate your help!
 
Last edited by a moderator:

Peter Bartholomew

Well-Known Member
A cunning trick for your amusement. Naming the numeric ranges to be 'range', allows the formula
= IMREAL(range&"i")
to give the lows and
= -IMAGINARY(range&"i")
gives the highs.
 

adamuce

Member
Hi !​
If cell B2 contains the range like '50-100' so the beginner level formula for cell​

  • B3 is =LEFT(B$2,SEARCH("-",B$2)-1)

  • B4 is =RIGHT(B$2,LEN(B$2)-SEARCH("-",B$2))
Hi After I used the formula to find the daily high and low, I tried to find the highest and lowest numbers for the whole week using the MIN and MAX formula, but the answer comes as 0.

Any idea how to solve this please?
 

Peter Bartholomew

Well-Known Member
Anything extracted using the text functions LEFT, RIGHT or MID will be returned as a text string. The function VALUE will coerce the text to a number, as will double negation.
=VALUE(LEFT(B$2,SEARCH("-",B$2)-1))
=--LEFT(B$2,SEARCH("-",B$2)-1)
 
Last edited by a moderator:

pecoflyer

Active Member
Perhaps post a sample sheet with some expected results calculated manually. At the hand of your very scarce information AGGREGATE might be the answer
 

adamuce

Member
Anything extracted using the text functions LEFT, RIGHT or MID will be returned as a text string. The function VALUE will coerce the text to a number, as will double negation.
=VALUE(LEFT(B$2,SEARCH("-",B$2)-1))
=--LEFT(B$2,SEARCH("-",B$2)-1)
Sorry for the late response but many thanks for the input and it worked!
 
Top