Formula produces wrong result when data cells filled programmatically
 

This Formula:

=(IF(E69/50<1,1,E69/50)*3*IF(E69<=0,0,1)*IF(A69=1,0,1)*IF(A69=10,0,1)* IF(A69
=20,0,1))*IF(A69=0,0,1)

Produces a wrong result when I use code to put the numbers in cells A and E.

I didn't write the formula, my job is to produce some fast and fancy (four
levels deep) sorting, but when I write all the data to a recordset and then
re-organize the rows, this formula (which is not transposed, all it's
references are in the same row) produces a wrong result.

For instance with 1 in cell A and 56 in cell E, the answer should be 0, but
it produces 3. It works fine when typing values in by hand.

I've checked that the number formatting of the cell is not being harmed, all
cells are keeping their correct format.
(Number, 0 decimals is what the client wants)

Bob

Formula produces wrong result when data cells filled programmatically
 

Works like a champ for me, I used the following to test it.
------------------
Sub TestIt()
Cells(69, 1).Value = 1
Cells(69, 5).Value = 56
End Sub
------------------
Tried this also with values of A67 = 1 and E67 = 56
------------------
Sub TestIt()
Cells(69, 1).Value = Cells(67, 1).Value
Cells(69, 5).Value = Cells(67, 5).Value
End Sub
------------------


"Bob Graham" wrote in message
...
This Formula:


=(IF(E69/50<1,1,E69/50)*3*IF(E69<=0,0,1)*IF(A69=1,0,1)*IF(A69=10,0,1)* IF(A69
=20,0,1))*IF(A69=0,0,1)

Produces a wrong result when I use code to put the numbers in cells A and
E.

I didn't write the formula, my job is to produce some fast and fancy (four
levels deep) sorting, but when I write all the data to a recordset and
then
re-organize the rows, this formula (which is not transposed, all it's
references are in the same row) produces a wrong result.

For instance with 1 in cell A and 56 in cell E, the answer should be 0,
but
it produces 3. It works fine when typing values in by hand.

I've checked that the number formatting of the cell is not being harmed,
all
cells are keeping their correct format.
(Number, 0 decimals is what the client wants)

Bob

Formula produces wrong result when data cells filled programmatically
 

I'm putting this stuff into a recordset for the advanced and easy sorting,
there seems to be a problem with the data coming back from the recordset as
text instead of numbers, and even though the cell format still says number,
and it displays correctly, it's not really a number.

I'll report back when I've figured it out.

Bob


"Tumbleweed" wrote in message
...
Works like a champ for me, I used the following to test it.
------------------
Sub TestIt()
Cells(69, 1).Value = 1
Cells(69, 5).Value = 56
End Sub
------------------
Tried this also with values of A67 = 1 and E67 = 56
------------------
Sub TestIt()
Cells(69, 1).Value = Cells(67, 1).Value
Cells(69, 5).Value = Cells(67, 5).Value
End Sub
------------------


"Bob Graham" wrote in message
...
This Formula:



=(IF(E69/50<1,1,E69/50)*3*IF(E69<=0,0,1)*IF(A69=1,0,1)*IF(A69=10,0,1)* IF(A69
=20,0,1))*IF(A69=0,0,1)

Produces a wrong result when I use code to put the numbers in cells A
and
E.

I didn't write the formula, my job is to produce some fast and fancy
(four
levels deep) sorting, but when I write all the data to a recordset and
then
re-organize the rows, this formula (which is not transposed, all it's
references are in the same row) produces a wrong result.

For instance with 1 in cell A and 56 in cell E, the answer should be 0,
but
it produces 3. It works fine when typing values in by hand.

I've checked that the number formatting of the cell is not being harmed,
all
cells are keeping their correct format.
(Number, 0 decimals is what the client wants)

Bob