# Computing Average in Reporting Services exclude values less than 0

 0 Hello - I have this for a textbox in a SQL Server Reporting Services report. This is to take the total average ``````=(Sum(iif(Fields!Q1Rating.Value>0,Fields!Q1Rating.Value,0))/(Count(Fields!Q1Rating.Value) - Sum(iif(Fields!Q1Rating.Value<0,1,0))) + Sum(iif(Fields!Q2Rating.Value>0,Fields!Q2Rating.Value,0))/(Count(Fields!Q2Rating.Value)) - Sum(iif(Fields!Q2Rating.Value<0,1,0)))+ Sum(iif(Fields!Q3Rating.Value>0,Fields!Q3Rating.Value,0))/(Count(Fields!Q3Rating.Value) - Sum(iif(Fields!Q3Rating.Value<0,1,0))) + Sum(iif(Fields!Q4Rating.Value>0,Fields!Q4Rating.Value,0))/(Count(Fields!Q4Rating.Value) - Sum(iif(Fields!Q4Rating.Value<0,1,0)))/4 `````` I don't want to divide by 0 because if the value for Q3Rating is -1 then it will be displayed as 0 and I want to exclude those values. It would be -1 if someone choose N/A and I am representing N/A as -1. more ▼ asked Oct 27, 2009 at 07:18 PM in Default Adam Olson 1 ● 1 ● 1 ● 1 Kristen ♦ 2.2k ● 6 ● 7 ● 10 add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users

 0 So, If you do not want to use values 0 or less, why not just exclude them as your first if does? Explain more about what you are after and you will get a response. You can always set it to 1. This way, you will avoid divide by zero errors and just come out with the original value.``=(Sum(iif(Fields!Q1Rating.Value>0,Fields!Q1Rating.Value,1))/(Count(Fields!Q1Rating.Value) - Sum(iif(Fields!Q1Rating.Value<0,1,1))) + Sum(iif(Fields!Q2Rating.Value>0,Fields!Q2Rating.Value,1))/(Count(Fields!Q2Rating.Value)) - Sum(iif(Fields!Q2Rating.Value<0,1,1)))+Sum(iif(Fields!Q3Rating.Value>0,Fields!Q3Rating.Value,1))/(Count(Fields!Q3Rating.Value) -Sum(iif(Fields!Q3Rating.Value<0,1,1))) +Sum(iif(Fields!Q4Rating.Value>0,Fields!Q4Rating.Value,1))/(Count(Fields!Q4Rating.Value) - Sum(iif(Fields!Q4Rating.Value<0,1,1)))/4`` more ▼ answered Nov 16, 2009 at 12:36 PM RickD 1.7k ● 1 ● 1 ● 4 add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users

### New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

By Email:

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1951
x94

asked: Oct 27, 2009 at 07:18 PM

Seen: 2951 times

Last Updated: Nov 16, 2009 at 01:30 PM