Show numbers nearly the same

hendrikbez

Board Regular
Joined
Dec 13, 2013
Messages
82
Is this possible.

I have a number that can change very time eg 0.00001234
Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004, 0.00000008, 0.00000016 (goes up by double each time.

I need that if the number on top (0.00001234) is about 100 less (0.00001134) or 100 (0.00001334) more, it must show that number in a color

is this possible
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,165
Office Version
  1. 365
Platform
  1. Windows
It was a bit tricky knowing your number format as in post 1 you used "." as your decimal separator and post 3 you used both "." and ",". ;)

With "," separator, my formula would become
=ABS($A$2-A4)<=0,000001
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

hendrikbez

Board Regular
Joined
Dec 13, 2013
Messages
82
I am sorry, it is my fault, I did not notice that the rest of them have a , and not a . in them, it should be a . in all of them.
Must get new spectacles, I cannot believe I did not see that it is different.
 

hendrikbez

Board Regular
Joined
Dec 13, 2013
Messages
82
I have done what you said, but it does not show the ones I need to be colored (see screenshot), and when I change the top one, the rule disappeared.
Is there a way to use vb for this.
 

Attachments

  • Screenshot_6.png
    Screenshot_6.png
    133.9 KB · Views: 4

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,134
Office Version
  1. 365
Platform
  1. Windows
You are still using a mix of decimal separators, so are your numbers actual numbers, or just text?
If you put
=isnumber(B4)
into an empty cell what does it return?
 

hendrikbez

Board Regular
Joined
Dec 13, 2013
Messages
82

ADVERTISEMENT

Fluff, I got it to work, thank you for your help
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,134
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it & thanks for the feedback.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,134
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

@hendrikbez
For future reference while we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

Watch MrExcel Video

Forum statistics

Threads
1,108,719
Messages
5,524,459
Members
409,581
Latest member
khin

This Week's Hot Topics

Top