Get rid of NaN for percentage calculation 1

[javedi]

Hello

I have used the expression below to calculate a percentage but when there is nothing to calculate a NaN is returned. Is there a way I can get over this?

=1-(Sum(Fields!MoreThan1Day.Value))/Count(Fields!TN.Value)

Thanks,
Javedi

 

[IanWaterman]

Try using an IIF

= IIF(Count(Fields!TN.Value)= 0, 1,1-(Sum(Fields!MoreThan1Day.Value))/Count(Fields!TN.Value))

Which transaltes to if Count(Fields!TN.Value)= 0 then 1 else 1-(Sum(Fields!MoreThan1Day.Value))/Count(Fields!TN.Value)

Ian

 

[javedi]

Thanks Ian, but it's now returning 100% for values which should be 0%. Any way around this?

 

[IanWaterman]

Try

= IIF(Count(Fields!TN.Value)= 0, 0,1-(Sum(Fields!MoreThan1Day.Value))/Count(Fields!TN.Value))

Ian

 

[lesp123]

Try "0, 0,1-...." instead of "0, 1,1-....". Or if you want blank cells use 0, "",1-....

 

[javedi]

Thanks Ian, that worked. Would you also happen to know how i can have the percentage to total accurately?

 

[IanWaterman]

Not sure what you mean.

Please show sample of data. Could be a rounding issue.

Ian

 

[javedi]

Here's an example, where the Total colum should show 35%.

one column
Jun 2012
10 0.00%
10 10.00%
4 25.00%
Total 24 8.33%

 

[IanWaterman]

What is your expression yielding 8.33% ?

You should ne able to do a simple sum of the expression holding the IIF() statement above

Ian

 

[javedi]

I tried the below but get the same result
= sum(IIF(Count(Fields!TN.Value)= 0, 0,1-(Sum(Fields!MoreThan1Day.Value))/Count(Fields!TN.Value)))

 

[xlbo]

You need to filter out the morethan1day values where TN = 0 on both sides:

=sum(IIF(Count(Fields!TN.Value)= 0, 0,1-(Sum(IIF(Count(Fields!TN.Value)= 0, 0,Fields!MoreThan1Day.Value)))/Count(Fields!TN.Value)))

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[javedi]

Thanks xlbo, the results were accurate as only 2 cases out of 24 were out of time.