Divide by zero- HELP

[ChrisSedgwick]

Hi,

I'm trying to run a query that calculates a figure over a certain number of months. The exact calculation is:

Contribution Value divided by Duration of Works

I then have the report set up to take into account the Anticipated Start Date. From this date it takes the results from the above calculation and splits the figure up over the duration.

I did have the calculation working initially, however now I want to multiply that result by the Probability. So if we say it's 50% probable that we will win the job. This affects the total result. Again all works fine on most of the records, apart from some which show #Div!0 or something to that effect. Then if I try to run the report, it gives me an error message saying "Divide by zero"

I'm not sure of any other way to get the query to not count the Div0's or if it can leave them blank.

Here's the formula

Contribution: IIf([Anticipated Duration of Our Workds]=0,0,IIf([Probability]=0,0,IIf([Quotes]![ContributionValue]=0,0,[Quotes]![ContributionValue]\[Project]![Anticipated Duration of Our Workds]*[Project]![Probability])))

I've also attached a screenshot of the query result. Could someone explain how i can get rid of the #Div/0!

Thanks,
Chris.

 

[plog]

IIf([Quotes]![ContributionValue]=0,0,[Quotes]![ContributionValue]\[Project]![Anticipated Duration of Our Workds]*[Project]![Probability])

Why are you checking the numerator for 0? You need to check the denominator (all parts of it) for 0.

Looking at it closer, you don't need a nested IIf Statement, you just need 1.

 

[ChrisSedgwick]

Hi,

I'm sorry, I don't follow. I got the IIf statement off the internet when I searched for help with the Divide by zero. It seemed to work for a short while until I added the multiplication.

Where do you believe I've gone wrong?

Thanks,

 

[ChrisSedgwick]

IIf([Quotes]![ContributionValue]=0,0,[Quotes]![ContributionValue]\[Project]![Anticipated Duration of Our Workds]*[Project]![Probability])

Looking at it closer, you don't need a nested IIf Statement, you just need 1.

So I just need to use the AND statement within the IIf statement to use it for all, or am I miles out here?

 

[plog]

 IIf([Anticipated Duration of Our Workds]=0

Yes you would use AND to check all parts of the denominator. You do not need to test the numerator at all:

0/X = 0

 

[ChrisSedgwick]

Contribution: IIf([Anticipated Duration of Our Workds] And [Project]![Probability] And [Quotes]![ContributionValue]=0,0,[Quotes]![ContributionValue]\[Project]![Anticipated Duration of Our Workds]*[Project]![Probability])

Something along these lines for the IIf statement?

 

[plog]

Llike I said before, you don't need to test the numerator. But you do explicitly need to compare everything else. You need to do a comparison on the Porbability--not just name.

 

[ChrisSedgwick]

I feel absolutely stupid, could you make it a little clearer?

At which point am I testing the numerator?

 

[ChrisSedgwick]

Contribution: IIf([Anticipated Duration of Our Workds] And [Project]![Probability] And [Quotes]![ContributionValue]>0,[Quotes]![ContributionValue]\[Project]![Anticipated Duration of Our Workds]*[Project]![Probability],0)

This any closer? Now it only shows me a few #Div/0! -- which is an improvement.

 

[plog]

[Anticipated Duration of Our Workds] And [Project]![Probability] And [Quotes]![ContributionValue]>0

That's your criteria argument. It has 3 parts: A, B & C. Only in C are you doing any tests/comparisons. Further, C is your numerator, which you do not need to test.

You want C/(A+B), but if A OR B is 0 you are getting an error. Set your criteria to do the appropriate test on the appropriate parts to route around that.