PDA

View Full Version : [SOLVED] Wrong result of my macro!



cruznovsky
01-18-2018, 11:46 AM
Hello!

I would like to ask you what is wrong in my code, because I do not know what happened.
In the table I have 2 columns: Salary and Has Kids (Do someone have children?).
My macro take Salary and answer from columns Has Kids (Yes or No) and give particularly TaxRate.
I have problem because for example: If I have Salary 3000 and "Yes" in columns Has Kids, macro give me that result: 0.01 (It should be 0).
Please help me, because I do not know what do I wrong.
Sorry for my English mistakes :)


Function getTaxRateElseNestedIF(Salary As Double, HasKids As String) As Double
If Salary > 5000 And Salary < 40000 Then
If HasKids = "Yes" Then
getTaxRateElseNestedIF = 0.15
Else
getTaxRateElseNestedIF = 0.25
End If
ElseIf Salary >= 40000 And Salary < 90000 Then
If HasKids = "Yes" Then
getTaxRateElseNestedIF = 0.28
Else
getTaxRateElseNestedIF = 0.35
End If
ElseIf Salary > 90000 Then
If HasKids = "Yes" Then
getTaxRateElseNestedIF = 0.42
Else
getTaxRateElseNestedIF = 0.45
End If
Else
If HasKids = "Yes" Then
getTaxRateElseNestedIF = 0
Else
getTaxRateElseNestedIF = 0.01
End If
End If
End Function

Paul_Hossler
01-18-2018, 12:29 PM
You can simplify it a little



Option Explicit
Function getTaxRate(Salary As Double, HasKids As String) As Double
Select Case Salary
Case Is > 90000
getTaxRate = IIf(HasKids = "Yes", 0.42, 0.45)
Case Is >= 40000
getTaxRate = IIf(HasKids = "Yes", 0.28, 0.35)
Case Is >= 5000
getTaxRate = IIf(HasKids = "Yes", 0.15, 0.25)
Case Else
getTaxRate = IIf(HasKids = "Yes", 0#, 0.01)
End Select
End Function

cruznovsky
01-18-2018, 12:35 PM
Your solution is shorter, but I have the same problem. I have 3,000.00 salary and Yes from column HasKids and I get result (0.01). I do not why. Show me mistake im my code or your code, or try put Yes in cell A1 and 3000 in A2 and try my or your code.

cruznovsky
01-18-2018, 12:52 PM
I tried this simple code to check my problem, but still I get result 0.01...Is it normal? S = 3000, H = Yes. Why do I get 0.01 result?!


Function test(S As Double, H As String) As Double
If S > 5000 And S < 40000 Then
If H = "Yes" Then
test = 0.15
Else
test = 0.25
End If
Else
If H = "Yes" Then
test = 0
Else
test = 0.01
End If
End If
End Function

Paul_Hossler
01-18-2018, 01:17 PM
Your AND logic is wrong - both pieces (S > 5000, S < 40000) must be true for the if to be True




Sub drv()
MsgBox test(3000, "Yes")
End Sub


'S = 3000, H = Yes
Function test(S As Double, H As String) As Double
If S > 5000 And S < 40000 Then ' The AND is not true since while 3000 < 40000, 3000 is NOT > 5000
If H = "Yes" Then
test = 0.15
Else
test = 0.25
End If
Else ' so it goes to here
If H = "Yes" Then ' H is YES, so
test = 0 ' the answer is 0
Else
test = 0.01
End If
End If
End Function




If 3000 has different rates, then



Option Explicit
Function getTaxRate(Salary As Double, HasKids As String) As Double
Select Case Salary
Case Is > 90000
getTaxRate = IIf(HasKids = "Yes", 0.42, 0.45)
Case Is >= 40000
getTaxRate = IIf(HasKids = "Yes", 0.28, 0.35)
Case Is >= 5000
getTaxRate = IIf(HasKids = "Yes", 0.15, 0.25)
Case Is >= 3000
getTaxRate = IIf(HasKids = "Yes", 0.09, 0.12)
Case Else
getTaxRate = IIf(HasKids = "Yes", 0#, 0.01)
End Select
End Function

cruznovsky
01-18-2018, 01:25 PM
Ok, I understand what do you try to say me. S=3000 so S > 5000 is not true, but S < 40000 is true, so it is problem, but why your code above is wrong and give me still result 0.01?


Option Explicit
Function getTaxRate(Salary As Double, HasKids As String) As Double
Select Case Salary
Case Is > 90000
getTaxRate = IIf(HasKids = "Yes", 0.42, 0.45)
Case Is >= 40000
getTaxRate = IIf(HasKids = "Yes", 0.28, 0.35)
Case Is >= 5000
getTaxRate = IIf(HasKids = "Yes", 0.15, 0.25)
Case Else
getTaxRate = IIf(HasKids = "Yes", 0#, 0.01)
End Select
End Function

cruznovsky
01-18-2018, 02:10 PM
Still give me wrong result. I do not know why. Simple case and big problem. Should we give up?

Paul_Hossler
01-18-2018, 02:15 PM
Ok, I understand what do you try to say me. S=3000 so S > 5000 is not true, but S < 40000 is true, so it is problem, but why your code above is wrong and give me still result 0.01?



Option Explicit
Function getTaxRate(Salary As Double, HasKids As String) As Double
Select Case Salary
Case Is > 90000
getTaxRate = IIf(HasKids = "Yes", 0.42, 0.45)
Case Is >= 40000
getTaxRate = IIf(HasKids = "Yes", 0.28, 0.35)
Case Is >= 5000
getTaxRate = IIf(HasKids = "Yes", 0.15, 0.25)
Case Else
getTaxRate = IIf(HasKids = "Yes", 0#, 0.01)
End Select
End Function



The way Select Case works is to test the 'selector' , Salary in this case

Is Salary > 90000 ... No, so try next

Is Salary > 40000 ... No, so try next

Is Salary > 5000 ... No, so try next


The 'next' is the Else, so the return is either 0 or .01


What should be the answer for Salary = 3000 ???

If 3000 has different rates, then


Option Explicit
Function getTaxRate(Salary As Double, HasKids As String) As Double
Select Case Salary
Case Is > 90000
getTaxRate = IIf(HasKids = "Yes", 0.42, 0.45)
Case Is >= 40000
getTaxRate = IIf(HasKids = "Yes", 0.28, 0.35)
Case Is >= 5000
getTaxRate = IIf(HasKids = "Yes", 0.15, 0.25)
Case Is >= 3000
getTaxRate = IIf(HasKids = "Yes", 0.09, 0.12)
Case Else
getTaxRate = IIf(HasKids = "Yes", 0#, 0.01)
End Select
End Function

Paul_Hossler
01-18-2018, 02:18 PM
Still give me wrong result. I do not know why. Simple case and big problem. Should we give up?

There's no need to give up. it's just a matter of understanding your requirement.

Right now, 3000 always gets the 'Else' answer of o or .01 -- what should it get?

cruznovsky
01-18-2018, 02:22 PM
If Salary = 3000 and Has Kids is "Yes" result should be 0. If Has Kids is "No" result = 0.01. In every code above my message I get result = 0.01 (Should be 0). Please do not talk about theory and test it in excel. Put in A1 "Yes", put in A2 3000 and in A3 use function. It is not normal in order to result be 0.01...I will go crazy if I will not find the solution of this problem.

Paul_Hossler
01-18-2018, 02:35 PM
21390


Well, they seem to work for me

cruznovsky
01-18-2018, 02:44 PM
Ok, so please look for my file and check code in cell E5.

Paul_Hossler
01-18-2018, 03:01 PM
"Yes " is not the same as "Yes" -- Fix your inputs to remove trailing spaces


21393


Please do not talk about theory and test it in excel.

You need / should add some error checking

cruznovsky
01-18-2018, 03:12 PM
OMG! Thank you so much! Thank you for show me how to check error. In the future I will set break point and I will analyse how to code working. Thank you again! I learned a lot of in this case and you very help me.