Find Percentage Of Two Values (Div/zero Errors!) Follow

0
Avatar
Legacy Poster

I have two price values stored as double that I want to compare and then print the percentage difference if it is more than 15% of the comparison price (CompPrice).

 

My current attempt is:

IIF(CompPrice<>0,IIF(CDbl(CompPrice - Price)>=CDbl(CompPrice*0.15),(100-((100/CompPrice)*Price)),0),0)

 

I keep getting a div/zero error though as CompPrice is sometimes zero. I thought that the first IIF would capture this and exit the statement but it doesn't seem to work like that?

 

I also want to round the result down to the nearest 5 but I don't know how to do this without adding more IIF statements!

3 comments

0
Avatar
Legacy Poster
Comment actions Permalink

Issue solved by using a multiline script with variables.

0
Avatar
Legacy Poster
Comment actions Permalink

How did you fix this? I'm trying to round to the nearest 10 and calculate the difference.

0
Avatar
Legacy Poster
Comment actions Permalink

Hi Rod,

I used the following code block as a multi-line script and it seems to work ok:

 

If (CompPrice <>0) then
    If(CDbl(CompPrice - Price )>=CDbl(CompPrice*0.15)) then
        x = (100-((100/CompNoZero)*Price))
        y = (Int(x/10))*10
        If(y < (x-5)) then
            Value = " Save over " + CStr(y+5)+ "% "    
        else
            Value = " Save over " + CStr(y)+ "% "    
        End If
    else
        Value = ""
    End If

else
    Value = ""
End If

 

There is probably a more elegant way of doing it, but this works for me. It is also adding in a text string to display as a saving but you could just remove that. Hope that helps!
 

Please sign in to leave a comment.