# Show numbers nearly the same

#### hendrikbez

##### Board Regular
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

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

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

#### Peter_SSs

##### MrExcel MVP, Moderator
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

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

Replies
8
Views
73
Replies
2
Views
46
Replies
4
Views
53
Replies
1
Views
46
Replies
0
Views
41