In a query - 0/0 = #Error - HELP

[lmn]

I have a customer who wants to what they call "normalize" data - i.e. - if a vendor has 100 projects - they are more likely to fail so they scale the average survey score to reflect that.

To do this I have to create like 4 queries (so far). What he did in Excel was take the Score of the survey and subtract the minimum of all the scores. He then divided that by the difference between the Max (of all scores) minus the Min (of all scores) and multiplied by 100.

I had to break this apart into several queries.

First I calculated in query 1 - the SURVEY SCORE - Min (from all of their scores).

I then calculated in query 2 - the Max (from all scores) - Min (from all scores)

I am trying to calculate in query 3 - The field calculated from Query 1/the field calculate from Query 2.

When I do this - some of them are taking 0/0. Instead of showing 0 - it is showing #Error. Is there any way to avoid this??????????

On another note - has anyone ever been requested to do this and have any suggestions? I personally think they should just rank their vendors based on raw scores but they want to use statistics. I'm a database developer (and not a good one at that) - not a statistician!!!

Thanks.

 

[Trendsetter]

It is mathematically impossible to have anything divided by zero that is why you are getting the error.
You coult test to ensure that the denominator is never zero, this could be done by using an IIf statement or checking in straight code the such a value is not zero

 

[lmn]

That's what I figured......I'll be writing an Iif statement - I guess I have to figure out what value to put in its place because the value would be included in an average and if I put it at zero - it's obviously going to make the score lower......maybe I'll just put the value to 999999 in the Iif statement and then exclude anythign that is 999999 when computing the average.

Thanks for being a voice of reason.....