• 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.

Cell highlighted in a given range

P

pucha

Member
Dear friends,

I have a range of price value (stock), where i am using two conditional formatting "between & inbetween" option to highlight a column range. Now i want to highlight the close price (single cell) on the same range as the third conditional formatting. how do i proceed for the same.

open high low close
1000 1200 950 980

range of high and low
high 1200
low 950

range (price movement between 1200 and 950
1200
1150
1110
...
...
...
1000
980
950

Now the close is at 980. so the cell I want to highlight is 980.

With Regards

Pucha
 
Hi, pucha!
Consider uploading a sample file. It'd be easier to understand for people who might be able to help you.
Regards!
 
If between any consecutive cell values then CF formula is.
=OR(MEDIAN($J$6,$L2,$L3)=$J$6,MEDIAN($J$6,$L1,$L2)=$J$6)
 
It should be closet to the cell value. I have attached another file please check.

There is an error uploading the file.
 
It should be closet to the cell value. I have attached another file please check.

There is an error uploading the file.
 

Attachments

  • Sample_1.xls
    32 KB · Views: 1
Try this then.
=OR(MAX(IF(($L$2:$L$26-$J$6)<0,$L$2:$L$26-$J$6))=$L2-$J$6,MIN(IF(($L$2:$L$26-$J$6)>=0,$L$2:$L$26-$J$6))=$L2-$J$6)
 
Thank you Chihiro. It is working . I was applying directly in formula. Thanks once again.

Well is it possible to highlight the nearest number in the range instead of two cell. if possible please let me know.

thanks
With Regards.
 
What do you mean?

Do you mean that you want only single cell, which has smallest variance from Closing value highlighted? (be it positive or negative)
 
Then just use...
=MIN(ABS($L$2:$L$26-$J$6))=ABS($L2-$J$6)

EDIT: Forgot the ABS to right of equal sign. :p
 
Last edited:
You must log in or register to reply here.
Back
Top