in support of this forum post
http://www.excelfox.com/forum/showth...ll=1#post13088


Explanation 1
In column K are numbers given to a maximum of 2 decimal places, for example
Column K
1090.69
147.95
264.47
30

The value in Column K must be adjusted so that it has the decimal format to 2 decimal places in steps of .05
So in this form, of like
…… 23.95 234 34.25 4.30 100.35 45.45 56.05 ……… etc….

So for example, in the above Column K test data, no adjustment is needed for 147.95 or 30
For 1090.69 and 264.47 some adjustment is needed. The adjustment could be to raise or lower the value. These are the possibilities:
change 1090.69 to 1090.65 or 1090.7
change 264.47 to 264.45 or 264.50

Which of the two adjustments is necessary will depend on the following:
If column H is greater than column D , then we adjust up .
If column H is lower than column D, then we adjust down .

Explanation 2
For all data rows, we compare column H to column D. If column H is greater than column D , then we adjust the value in column K up to the nearest multiple of .05. If column H is less than column D , then we adjust the value in column K down to the nearest multiple of .05. ( If the value in column K is an exact multiple of .05, then no action is to be taken )

For example
Before:
Row\Col
D
E
F
G
H
I
J
K
2
1087
1088
1077.25
1067.25
1079.9
25
10.799
1090.69
3
148.05
149.9
146.5
146
146.5
22
1.465
147.95
4
265
269.3
265
262.85
267.15
15083
2.6715
264.47
5
30.4
30.4
29.8
29.65
29.95
17388
0.2995
30


After:
Row\Col
D
E
F
G
H
I
J
K
L
2
1087
1088
1077.25
1067.25
1079.9
25
10.799
1090.65
 This nuber is adjusted down
3
148.05
149.9
146.5
146
146.5
22
1.465
147.95
 This number is not changed
4
265
269.3
265
262.85
267.15
15083
2.6715
264.5
 This number is adjusted up
5
30.4
30.4
29.8
29.65
29.95
17388
0.2995
30
 This number is not changed





Solution ( guess )

The previous formula solution already always adjust number down,


Row\Col
D
H
K
L
M
N
O
P
2
1087
1079.9
1090.69
21813.8
21813
1090.65
1090.65
1090.65
3
148.05
146.5
147.95
2959
2959
147.95
147.95
147.95
4
265
267.15
264.47
5289.4
5289
264.45
264.45
264.45
5
30.4
29.95
30
600
600
30
30
30

Row\Col
D
H
K
L
M
N
O
P
2
1087
1079.9
1090.69
=K2*100/5
=INT(L2)
=M2*5/100
=INT(L2)*5/100
=INT(K2*100/5)*5/100
3
148.05
146.5
147.95
=K3*100/5
=INT(L3)
=M3*5/100
=INT(L3)*5/100
=INT(K3*100/5)*5/100
4
265
267.15
264.47
=K4*100/5
=INT(L4)
=M4*5/100
=INT(L4)*5/100
=INT(K4*100/5)*5/100
5
30.4
29.95
30
=K5*100/5
=INT(L5)
=M5*5/100
=INT(L5)*5/100
=INT(K5*100/5)*5/100


So previous solution is correct if H < D
If H > D , the previous solution is .05 too small , so previous solution must be adjusted by +.05
=IF(H2<D2,INT(K2*100/5)*5/100,IF(H2>D2,(INT(K2*100/5)*5/100)+0.05,"H is equal to D"))
=IF(H3<D3,INT(K3*100/5)*5/100,IF(H3>D3,(INT(K3*100/5)*5/100)+0.05,"H is equal to D"))
=IF(H4<D4,INT(K4*100/5)*5/100,IF(H4>D4,(INT(K4*100/5)*5/100)+0.05,"H is equal to D"))
=IF(H5<D5,INT(K5*100/5)*5/100,IF(H5>D5,(INT(K5*100/5)*5/100)+0.05,"H is equal to D"))


But we must also check if number is already exact multiple of .05
Like if ( integer (value/.05)) – value/.05) = 0
( Excel has errors and bugs, and may give a very small number when it should give us 0, so we must do a trick-
if Round ( ( integer (value/.05)) – value/.05) ) = 0 )
So:
=IF(ROUND(INT(K2/0.05)-(K2/0.05),2)=0,K2,IF(H2<D2,INT(K2*100/5)*5/100,IF(H2>D2,(INT(K2*100/5)*5/100)+0.05,"H is equal to D")))
=IF(ROUND(INT(K3/0.05)-(K3/0.05),2)=0,K3,IF(H3<D3,INT(K3*100/5)*5/100,IF(H3>D3,(INT(K3*100/5)*5/100)+0.05,"H is equal to D")))
=IF(ROUND(INT(K4/0.05)-(K4/0.05),2)=0,K4,IF(H4<D4,INT(K4*100/5)*5/100,IF(H4>D4,(INT(K4*100/5)*5/100)+0.05,"H is equal to D")))
=IF(ROUND(INT(K5/0.05)-(K5/0.05),2)=0,K5,IF(H5<D5,INT(K5*100/5)*5/100,IF(H5>D5,(INT(K5*100/5)*5/100)+0.05,"H is equal to D")))

1090.65
147.95
264.5
30