Sums have error in answer

General
1

Good evening, I’ve looked in all the tutorials and don’t find an answer for this. I have a list box with three columns and three rows. There are numbers in most of the dells. When I add (sum) the numbers as rows I get a number that agrees when I add the numbers individually as a check. When I sum the numbers as columns my number does not agree with the answer the program is giving. I’m off by 1.35 on 6,123.33, 1.16 on 5,832.16 and 1.49 on 11,955.49 which mean little except that the errors aren’t huge but still errors I didn’t expect.

So adding across as rows provides a correct answer all three times, but adding down gives errors. Would anyone know why this is happening? I am using numbers that I enter as general numbers, not currency, to the second place after the decimal.

Any information would be greatly appreciated. Thank you.

2

Can you create a sample showing a simulation of your problem?

3

Are you still using Val as we’d determined in your other thread?

Converting user input numbers to a numeric data type like Integer or Double needs to be done with Cdbl as has been documented for as long as I can remember.

4

I did originally use Val but changed to CDbl . The code I used is this:

// All recordSets have been added to the CalculationsLB and must be tallied to provide
// a total to be placed in the appropriate cell of the ExpensesLB
Dim total as Double
For i = 0 to CalculationsLB.LastRowIndex
total = total + CDbl(CalculationsLB.Cell(i,4))
Next

txtTotal.text = Format(total, “###,###,##0.00”)
//Return

The list box looks like this showing rows and columns. The last number in the rows and columns are the summed values.

325.49 325.49
3,245.62 3,245.62 6,491.24
2,552.22 2,552.22 5,104.44
34.32 34.32

6,122.00 5,831.00 19,954.00

I hadn’t noticed it before now but the totals for the adding of the columns all end in a number with adouble zero after the decimal which is incorrect. These numbers are also CDbl. Does this help?

Again, I appreciate your insight.

5

Not really. We need a sample project that we can download and run to see the same as what you see and not try to guess with just parts of your code.

For example, you said:

but the code you posted has this:

so now is hard to guess what you are doing with CalculationsLB.Cell(i, 4)
4 is outside of a 3 column by 3 row Listbox.

6

AlbertoD, appreciate your efforts. I’ll have to try making a small sample project. Never have done one so kindly give me time. Wasn’t aware that what I sent would show as it did. Was different when I sent.

7

You are using the double data type which means floating point math and that has a limited accuracy.

Try switching to a fixed precision data type such as currency.

8

I created a window with a listbox.
Listbox had 3 columns

In the Open event, I populated the listbox with your data above, then summed the first two columns into the third.
And put the grand total at the end.
Is there something wrong with the values?
(bear in mind that 6,122.00 + 5,831.00 is not 19,954.00 … unsure what is happening in your data?)


dim lb as listbox = self.listbox1


lb.addrow ""
lb.cell(lb.LastAddedRowIndex,0) = "325.49"

lb.addrow ""
lb.cell(lb.LastAddedRowIndex,0) = "3,245.62"
lb.cell(lb.LastAddedRowIndex,1) = "3,245.62"


lb.addrow ""
lb.cell(lb.LastAddedRowIndex,0) = "2,552.22"
lb.cell(lb.LastAddedRowIndex,1) = "2,552.22"

lb.addrow ""
lb.cell(lb.LastAddedRowIndex,0) = "34.32"

lb.addrow ""
lb.cell(lb.LastAddedRowIndex,0) = "6,122.00"
lb.cell(lb.LastAddedRowIndex,1) = "5,831.00"



dim x as integer
dim subtot as double
dim runtot as double

for x = 0 to lb.LastRowIndex
  subtot = cdbl(lb.cell(x,0)) + cdbl(lb.cell(x,1)) 
  runtot = runtot + subtot
  lb.cell(x,2) = format(subtot,"0.00")
next

lb.addrow ""
lb.cell(lb.LastAddedRowIndex,2) = format(runtot,"0.00")

image
image1254×872 57.3 KB

9

you could save the original data in celltag as currency.
you can also custom draw a cell with the celltag data, instead of working with strings.

xojo listbox cell should be variant/object type…