Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hide a formula result until cell has been filled | Excel Discussion (Misc queries) | |||
Result of Formula produces Picture? | Excel Worksheet Functions | |||
Fill down produces correct formula but wrong answer | Excel Worksheet Functions | |||
FORMULA PRODUCES WRONG RESULT | Excel Discussion (Misc queries) | |||
Copy Paste of Formula Produces Incorrect Result | Excel Discussion (Misc queries) |