Mathematical conundrum

[mikejowz]

I have created an Excel Spreadsheet to categorize expenses that show on my downloaded bank statement.
Occasionally a sum has to be split into 2 or 3 categories and I have a user form with 3 text boxes and 3 combo boxes where I can enter the split details. (amounts and categories).
Before this data is transferred to the spreadsheet a check is carried out to ensure the sum of the split amounts equals the original amount.
The variable holding the original amount is called startvalue and the error message is contained in another userform called SplitDoesntAddUp
The code for the check is...
If StartValue <> (Val(TextBox4.Text) + Val(TextBox5.Text) + Val(TextBox6.Text)) Then
SplitDoesntAddUp.Show
Exit Sub

This has always worked perfectly except...today I had an expense of 241.08 that I split as 200 + 41.08 and this threw up the error message.
If I split it as 200.01+41.07...also error
If I split it as 199.99+41.09 ...no error
If I split it as 200.02+ 41.06...no error
If I split it as 200+40+1.08...no error

I have subsequently created a message box that shows (Val(TextBox4.Text) + Val(TextBox5.Text) + Val(TextBox6.Text)) before running the check code as shown above.

When I split the amount as 200+41.08 or 200.01+41.07 the message box returns 241.08 but still shows the error message on the next step.

The split code is still working normally for other items on my bank statement

I have closed Excel and reopened it but the conundrum remains.

Does anyone have any ideas of what I should be looking for that might be creating this error?

 

[6StringJazzer]

This is caused by the inability of a binary computer to do perfect arithmetic on some real numbers. This causes problems when using relational operators. This is a very well understood problem in computer science, not a problem specific to Excel.

The solution is to explicitly round your numbers to the nearest cent when doing comparisons.

If Round(StartValue,2) <> (Round(Val(TextBox4.Text),2) + Round(Val(TextBox5.Text),2) + Round(Val(TextBox6.Text),2)) Then

Please try that and let us know if it works.

 

[mikejowz]

This is caused by the inability of a binary computer to do perfect arithmetic on some real numbers. This causes problems when using relational operators. This is a very well understood problem in computer science, not a problem specific to Excel.

The solution is to explicitly round your numbers to the nearest cent when doing comparisons.

If Round(StartValue,2) <> (Round(Val(TextBox4.Text),2) + Round(Val(TextBox5.Text),2) + Round(Val(TextBox6.Text),2)) Then

Please try that and let us know if it works.

Thankyou 6StringJazzer...have done that but no joy...still the error.

 

[pjmorris]

Hi,

I’d offer two options:
1. Change

StartValue <> (Val(TextBox4.Text) + Val(TextBox5.Text) + Val(TextBox6.Text))

to

ABS(StartValue -(Val(TextBox4.Text) -Val(TextBox5.Text) -Val(TextBox6.Text)))<0.005

2. In ‘options’ tick the option in ‘calculations’ to ‘use precision as displayed’, though I’m not sure whether that works in VBA.

Hope these help, unfortunately I’m doing this on an iPad so can’t test.

Regards

 

[mikejowz]

Hi,

I’d offer two options:
1. Change

StartValue <> (Val(TextBox4.Text) + Val(TextBox5.Text) + Val(TextBox6.Text))

to

ABS(StartValue -(Val(TextBox4.Text) -Val(TextBox5.Text) -Val(TextBox6.Text)))<0.005

2. In ‘options’ tick the option in ‘calculations’ to ‘use precision as displayed’, though I’m not sure whether that works in VBA.

Hope these help, unfortunately I’m doing this on an iPad so can’t test.

Regards

Thankyou PJMorris. I tried both those options. Tried #2 first...no joy.
I presume it was meant to go in the If/Then statement so this is what I tried...

'If StartValue <> (Val(TextBox4.Text) + Val(TextBox5.Text) + Val(TextBox6.Text)) Then
'If Round(StartValue, 2) <> (Round(Val(TextBox4.Text), 2) + Round(Val(TextBox5.Text), 2) + Round(Val(TextBox6.Text), 2)) Then
If Abs(StartValue - (Val(TextBox4.Text) - Val(TextBox5.Text) - Val(TextBox6.Text))) < 0.005 Then

It accepted 200+41.08 !!! BUT it accepted 1+1 also...so unfortunately fails as an error check.

 

[pjmorris]

Hmmm, I’m perhaps not surprised that #2 failed.

However, I would expect the if statement to work. That said, your version:

If Abs(StartValue - (Val(TextBox4.Text) - Val(TextBox5.Text) - Val(TextBox6.Text))) < 0.005 Then

might be better written:

If Abs(StartValue - Val(TextBox4.Text) - Val(TextBox5.Text) - Val(TextBox6.Text)) < 0.005 Then

.

I think it had an unnecessary pair of brackets.

If that doesn’t work, add a line of code to put both sets of values into cells on the sheet and format them as general. Then carefully inspect and compare them and I’d expect that to show you where the problem lies.

 

[mikejowz]

Hmmm, I’m perhaps not surprised that #2 failed.

However, I would expect the if statement to work. That said, your version:

If Abs(StartValue - (Val(TextBox4.Text) - Val(TextBox5.Text) - Val(TextBox6.Text))) < 0.005 Then

might be better written:

If Abs(StartValue - Val(TextBox4.Text) - Val(TextBox5.Text) - Val(TextBox6.Text)) < 0.005 Then

.

I think it had an unnecessary pair of brackets.

If that doesn’t work, add a line of code to put both sets of values into cells on the sheet and format them as general. Then carefully inspect and compare them and I’d expect that to show you where the problem lies.

I think we've solved it.

Removing the extra brackets made a difference BUT now the error shows for any correct sum and any incorrect sum is accepted.
So I flipped the <0.005 to >0.005 and IT WORKS!
This is the line of code that works...

If Abs(StartValue - Val(TextBox4.Text) - Val(TextBox5.Text) - Val(TextBox6.Text)) > 0.005 Then

Just to test the bracket theory I went back to my original test and removed the same extra brackets....still no dice!

So I think 6StringJazzer was right with his theory about binary computers struggling with some numbers..."a very well understood problem in computer science". I won't be giving up my day job for a career in computer science anytime soon if that's what you have to contend with.
But it seems ABS was the right way to resolve it rather than ROUND.
Getting rid of surplus brackets helped but it took the Kiwi to spot the < vs > error at the end.
So the scoreboard reads....
6 StringJazzer 1 (the computer science conundrum)
The Kiwi 1 (the > rather than <)
pjmorris 2 (ABS and the extra brackets)

Thankyou both for your contribution.