How to properly use Round() in variable

building reports webintelligence
1

Hey Everyone!!

I have a variable “Crop Year” which contains a simple If Else logic.

“=If [Prev Crop Year Formula]=0 And [Curr Crop Year Formula] = 0 Then 0
Else If [Prev Crop Year Formula] = 0 And [Curr Crop Year Formula] > 0 Then “100”
Else (([Crop Year Variance]/[Prev Crop Year Formula])*100)”

The result is sometimes a decimal “47.14”. The requirement is to round the number to the nearest whole number which the Round() function can do.

However, when I simply place the Round function in front of the variable it constantly throws an error saying there are missing parenthesis. I tried all formats I can possibly think of.

Any idea how to use this Round() function properly within the variable?

Thanks in advance :smiley:

MHassan (BOB member since 2013-11-19)

2

Surely Sum ought to be part of this variable too?

Mak 1 :uk: (BOB member since 2005-01-06)

3

Hmm, not to my knowledge. Its simply declaring if certain columns = 0 then display 0, else if one of the columns is 0 and one isnt then display 100, else divide 2 values and display the result.

No sum to my knowledge.

Thanks!

MHassan (BOB member since 2013-11-19)

4

just right click on the cell and format number and pick how many decimal places you want. no need to specifically use the round() function just for display purposes. this would be needed if you were rounding something to use in another calculation, however.

also your variable is sometimes returning a string “100” and sometimes a number 0 or the result of a division, so round() wouldn’t work on a string, and also, i’m not sure if the format number would work either in that case.

you could use condition formatting rules (alerters) to display the 0 or 100 and then just use a division in the variable instead of coding that logic in the variable.

erik.stenson :us: (BOB member since 2012-07-30)

5

That’s a very good point!!

It looked like the issue was the fact that it was returning a string at times.

I was able to fix it but this is what I did. I simply included the Round() function at the final “Else” condition. Since the first two conditions can’t return a decimal, its either 0 or 100, they didn’t need to be rounded.

Thanks for all your help guys!!

This is what the variable looks like now:

“=If [Prev Crop Year Formula]=0 And [Curr Crop Year Formula] = 0 Then 0
Else If [Prev Crop Year Formula] = 0 And [Curr Crop Year Formula] > 0 Then “100”
Else (Round ([Crop Year Variance]/[Prev Crop Year Formula];2)*100)”

MHassan (BOB member since 2013-11-19)