Hi,

I have a worksheet as below:

columnA columnB columnC columnD columnE

48.00 44.00 44.00 48.00

in the VBE, i will create a macro that will insert a formula into columnE.

this is my formula:

cells(1,5).formula="=IF(AND(MOD(AVERAGE(A1:D1),1)<0.1,AVERAGE(A1:D1)>1),TRUNC(AVERAGE(A1:D1)),ROUNDUP(AVERAGE(A1:D1),0)))"

(the value in cells(1,5) should be 46.00)

then i write a coding to change the value in cells(1,5) :

if cells(1,5) mod 4 <1 then

cells(1,5).formula="=IF(AND(MOD(AVERAGE(A1:D1),1)<0.1,AVERAGE(A1:D1)>1),TRUNC(AVERAGE

(A1:D1)),ROUNDUP(AVERAGE(A1:D1),0)))"

elseif cells(1,5) mod 4 <2 then

cells(1,5).formula="=IF(AND(MOD(AVERAGE(A1:D1),1)<0.1,AVERAGE(A1:D1)>1),TRUNC(AVERAGE

(A1:D1)),ROUNDUP(AVERAGE(A1:D1),0)))-1"

elseif cells(1,5) mod 4<3 then

cells(1,5).formula="=IF(AND(MOD(AVERAGE(A1:D1),1)<0.1,AVERAGE(A1:D1)>1),TRUNC(AVERAGE

(A1:D1)),ROUNDUP(AVERAGE(A1:D1),0)))+2"

else

cells(1,5).formula="=IF(AND(MOD(AVERAGE(A1:D1),1)<0.1,AVERAGE(A1:D1)>1),TRUNC(AVERAGE

(A1:D1)),ROUNDUP(AVERAGE(A1:D1),0)))+1"

end if

The coding above meaning it will round the number.

For example, if the value is 5, it will be rounded to 4

if the value is 7, it will be rounded to 8

if the value is 6, it will be rounded to 8

if the value is 3, it will be rounded to 4

so, after i run the macro, the final value for cells(1,5) should be 48.00. Agree?

Now the value in cells(1.5) is 48.00, it will add 2 to the 46.00.

The problem is when i change the value in cells(1,1) to 100.00, the value in cells(1,5) is 61.00. By right the value should be 60.00. What can i do to the coding so that i will get the value of 60.00 in cells(1,5) when i change the value in cells(1,1) to 100. (Meaning i don't wan to run the macro again, i just have to run the macro once, and in the future, when i change the value in cells(1,1), it will gives me the correct value for the cells(1,5) ).

Anyone know how to do it?

cheers,

brandon

like:

"if(mod(" & cells(1,5).value & ",4)<1," & _

"IF(AND(MOD(AVERAGE(A1:D1)

(A1:D1)),ROUNDUP(AVERAGE(A

",if(mod("& cells(1,5).value & ",4)<2," &

"IF(AND(MOD(AVERAGE(A1:D1)

(A1:D1)),ROUNDUP(AVERAGE(A

and so on.

you can include the old cells value in your formula "the hard way", and thus, it should work.

ok, you formula gets a little bit longer, but whatever..