Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Get rid of NaN for percentage calculation 1

Status
Not open for further replies.

javedi

Technical User
Apr 30, 2007
196
GB
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
 
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


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

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

Ian
 
Try "0, 0,1-...." instead of "0, 1,1-....". Or if you want blank cells use 0, "",1-....
 
Thanks Ian, that worked. Would you also happen to know how i can have the percentage to total accurately?
 
Not sure what you mean.

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

Ian
 
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%
 
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
 
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)))
 
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
 
Thanks xlbo, the results were accurate as only 2 cases out of 24 were out of time.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top