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
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

ChrisFoster

Board Regular
Joined
Jun 21, 2019
Messages
126
Office Version
  1. 365
Platform
  1. Windows
Yes, you can use conditional formatting to do that. It will allow you to change the colour for numbers between a specified range.
 

hendrikbez

Board Regular
Joined
Dec 13, 2013
Messages
82
How will conditional formatting work on this.

The number can change in line Amount 0.00001771. So I want the numbers to be like the red on below when the Amount is 0.00001771, 100 less or 100 more, if I change the Amount number, the color must change to the next high or low number.

Amount 0.000001771

1 0,00000001 0,00000002 0,00000003 0,00000004 0,00000005 0,00000010 0,00000020 0,00000030 0,00000040 0,00000050 0,00000100 0,00000200 0,00000300 0,00000400
2 0,00000002 0,00000004 0,00000006 0,00000008 0,00000010 0,00000020 0,00000040 0,00000060 0,00000080 0,00000100 0,00000200 0,00000400 0,00000600 0,00000800
3 0,00000004 0,00000008 0,00000012 0,00000016 0,00000020 0,00000040 0,00000080 0,00000120 0,00000160 0,00000200 0,00000400 0,00000800 0,00001200 0,00001671
4 0,00000008 0,00000016 0,00000024 0,00000032 0,00000040 0,00000080 0,00000160 0,00000240 0,00000320 0,00000400 0,00000800 0,00001881 0,00002400 0,00003200
5 0,00000016 0,00000032 0,00000048 0,00000064 0,00000080 0,00000160 0,00000320 0,00000480 0,00000640 0,00000800 0,00001671 0,00003200 0,00004800 0,00006400
6 0,00000032 0,00000064 0,00000096 0,00000128 0,00000160 0,00000320 0,00000640 0,00000960 0,00001280 0,00001671 0,00003200 0,00006400 0,00009600 0,00012800
7 0,00000064 0,00000128 0,00000192 0,00000256 0,00000320 0,00000640 0,00001280 0,00001871 0,00002560 0,00003200 0,00006400 0,00012800 0,00019200 0,00025600
8 0,00000128 0,00000256 0,00000384 0,00000512 0,00000640 0,00001280 0,00002560 0,00003840 0,00005120 0,00006400 0,00012800 0,00025600 0,00038400 0,00051200
9 0,00000256 0,00000512 0,00000768 0,00001024 0,00001280 0,00002560 0,00005120 0,00007680 0,00010240 0,00012800 0,00025600 0,00051200 0,00076800 0,00102400
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,065
Office Version
  1. 365
Platform
  1. Windows
Maybe something like
+Fluff New.xlsm
ABCDEFGHIJKLMN
1
20.00001771
3
40.000000010.000000020.000000030.000000040.000000050.000000100.000000200.000000300.000000400.000000500.000001000.000002000.000003000.00000400
50.000000020.000000040.000000060.000000080.000000100.000000200.000000400.000000600.000000800.000001000.000002000.000004000.000006000.00000800
60.000000040.000000080.000000120.000000160.000000200.000000400.000000800.000001200.000001600.000002000.000004000.000008000.000012000.00001671
70.000000080.000000160.000000240.000000320.000000400.000000800.000001600.000002400.000003200.000004000.000008000.000018810.000024000.00003200
80.000000160.000000320.000000480.000000640.000000800.000001600.000003200.000004800.000006400.000008000.000016710.000032000.000048000.00006400
90.000000320.000000640.000000960.000001280.000001600.000003200.000006400.000009600.000012800.000016710.000032000.000064000.000096000.00012800
100.000000640.000001280.000001920.000002560.000003200.000006400.000012800.000018710.000025600.000032000.000064000.000128000.000192000.00025600
110.000001280.000002560.000003840.000005120.000006400.000012800.000025600.000038400.000051200.000064000.000128000.000256000.000384000.00051200
120.000002560.000005120.000007680.000010240.000012800.000025600.000051200.000076800.000102400.000128000.000256000.000512000.000768000.00102400
Main
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A4:N12Expression=AND(A4>=$A$2-0.000001,A4<=$A$2+0.000001)textNO
 

hendrikbez

Board Regular
Joined
Dec 13, 2013
Messages
82

ADVERTISEMENT

Hi Fluff, yes like this, I will look if I can get it to work.
Thank you
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,165
Office Version
  1. 365
Platform
  1. Windows
Same concept, CF formula written a different way

20 07 28.xlsm
ABCDEFGHIJKLMN
1
20.00001771
3
40.000000010.000000020.000000030.000000040.000000050.00000010.00000020.00000030.00000040.00000050.0000010.0000020.0000030.000004
50.000000020.000000040.000000060.000000080.00000010.00000020.00000040.00000060.00000080.0000010.0000020.0000040.0000060.000008
60.000000040.000000080.000000120.000000160.00000020.00000040.00000080.00000120.00000160.0000020.0000040.0000080.0000120.00001671
70.000000080.000000160.000000240.000000320.00000040.00000080.00000160.00000240.00000320.0000040.0000080.000018810.0000240.000032
80.000000160.000000320.000000480.000000640.00000080.00000160.00000320.00000480.00000640.0000080.000016710.0000320.0000480.000064
90.000000320.000000640.000000960.000001280.00000160.00000320.00000640.00000960.00001280.000016710.0000320.0000640.0000960.000128
100.000000640.000001280.000001920.000002560.00000320.00000640.00001280.000018710.00002560.0000320.0000640.0001280.0001920.000256
110.000001280.000002560.000003840.000005120.00000640.00001280.00002560.00003840.00005120.0000640.0001280.0002560.0003840.000512
120.000002560.000005120.000007680.000010240.00001280.00002560.00005120.00007680.00010240.0001280.0002560.0005120.0007680.001024
CF Close
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A4:N12Expression=ABS($A$2-A4)<=0.000001textNO
 

hendrikbez

Board Regular
Joined
Dec 13, 2013
Messages
82

ADVERTISEMENT

Hi I am looking at this, but do not know what to do,
I click the button Conditional Formatting, but which one do I use, when creating new rule.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,165
Office Version
  1. 365
Platform
  1. Windows
For the layout shown in Fluff's and my posts,

  1. Select from A4 down to N12
  2. Home ribbon tab -> Conditional Formatting -> New Rule .. -> Use a formula to determine which cells to format -> Format values where this formula is true: -> Enter the formula suggested in that box -> Click 'Format...' -> Fill tab -> Choose colour -> OK -> OK
 

hendrikbez

Board Regular
Joined
Dec 13, 2013
Messages
82
I think I have got it, cannot add file to show it.
The only thing is the the number in A2 will change every few minutes, how do I get it to update the rest of the numbers to do the same.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,165
Office Version
  1. 365
Platform
  1. Windows
You cannot attach an actual file. However, you can use XL2BB to show part of your sheet like Fluff & I have done.

The highlights will change accordingly automatically every time the value in cell A2 changes. For example, this is the exact sheet I showed above and all I have done is change the value in A2.

20 07 28.xlsm
ABCDEFGHIJKLMN
1
20.00000164
3
40.000000010.000000020.000000030.000000040.000000050.00000010.00000020.00000030.00000040.00000050.0000010.0000020.0000030.000004
50.000000020.000000040.000000060.000000080.00000010.00000020.00000040.00000060.00000080.0000010.0000020.0000040.0000060.000008
60.000000040.000000080.000000120.000000160.00000020.00000040.00000080.00000120.00000160.0000020.0000040.0000080.0000120.00001671
70.000000080.000000160.000000240.000000320.00000040.00000080.00000160.00000240.00000320.0000040.0000080.000018810.0000240.000032
80.000000160.000000320.000000480.000000640.00000080.00000160.00000320.00000480.00000640.0000080.000016710.0000320.0000480.000064
90.000000320.000000640.000000960.000001280.00000160.00000320.00000640.00000960.00001280.000016710.0000320.0000640.0000960.000128
100.000000640.000001280.000001920.000002560.00000320.00000640.00001280.000018710.00002560.0000320.0000640.0001280.0001920.000256
110.000001280.000002560.000003840.000005120.00000640.00001280.00002560.00003840.00005120.0000640.0001280.0002560.0003840.000512
120.000002560.000005120.000007680.000010240.00001280.00002560.00005120.00007680.00010240.0001280.0002560.0005120.0007680.001024
CF Close
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A4:N12Expression=ABS($A$2-A4)<=0.000001textNO
 

Watch MrExcel Video

Forum statistics

Threads
1,108,598
Messages
5,523,812
Members
409,535
Latest member
chiefskingdom98

This Week's Hot Topics

Top