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
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

ChrisFoster

Board Regular
Joined
Jun 21, 2019
Messages
160
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
73,297
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
54,395
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
54,395
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
54,395
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,302
Messages
5,836,504
Members
430,436
Latest member
fefenouil

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top