vba question

[Pinkyhello]

Hello all,

i am trying to write a program where if the 3 cells in percentage adds up to 1 or 100 % then hightlight the 3 rows with %

i find that excel could read most of them except a particular combination


for below it's ok and can highlight rows in this order:
20%+10%+70%
70%+10%+20%
60%+10%+30%
30%+10%+60%
45%+45%+10%

but if the sequence change to, it cannot do the highlight and treat it as not =1
70%+20%+10%
20%+70%+10%
30%+60%+10%
60%+30%+10%

what could the problem be? i totally have no idea

 thanks

 

[StephenCrump]

This is probably a rounding error - the three amounts don't add up exactly to 1.

This may be relevant: Floating-point arithmetic may give inaccurate result in Excel - Microsoft 365 Apps

 

[Pinkyhello]

This is probably a rounding error - the three amounts don't add up exactly to 1.

This may be relevant: Floating-point arithmetic may give inaccurate result in Excel - Microsoft 365 Apps

but when i use msgbox to display the sum result , it does show 1 , but dunt know why it doesnt work

 

[StephenCrump]

Just to illustrate:

Book1
	A	B	C
1	70%	20%	10%
Sheet1

Sub Test()

    Dim d As Double
    
    d = Range("A1").Value + Range("B1").Value + Range("C1").Value
    MsgBox d
    MsgBox d = 1
    MsgBox d - 1
    
End Sub

 

[Pinkyhello]

Just to illustrate:

Book1
	A	B	C
1	70%	20%	10%
Sheet1

Sub Test()

    Dim d As Double
   
    d = Range("A1").Value + Range("B1").Value + Range("C1").Value
    MsgBox d
    MsgBox d = 1
    MsgBox d - 1
   
End Sub

ok i get it now, so maybe i will amend the formulas a bit, so it dont involve adding up percentages

thanks for the help!

 

[StephenCrump]

maybe i will amend the formulas a bit, so it dont involve adding up percentages

Just be aware that small rounding discrepancies can arise, not just with percentages.

So if you're testing for equality, you may need to round first.

 

[Pinkyhello]

Noted, will do rounding first next time if i do equality, thanks a lot