Divide by 0 problems using IIF

S

suek

Issue is that I need to build a form where users enter Key Performance
Indicators.
There are 4 fields: tender, design, construction and commissioning upon
which I am measuring planned vs actual performance. Each of these tasks has
a weighting: tender .1, design .35, construction .45 and commissioning .1

My IIF is working ok where they are on target or where they are behind
target (eg for the design value I have hidden a field with the following
calculation:


Year vs Planned To Date Design:
IIf([intYearPlannedDesign]=0,([intPlannedtoDateDesign]*0.35),(([intPlannedtoDateDesign]/[intYearPlannedDesign])*0.35))

and then shown on the form the total auto calc being:

Total Percentage Job Planned To date KPI: (([Year vs Planned To Date
Tender]+[Year vs Planned To Date Design]+[Year vs Planned To Date
Construction]+[Year vs Planned To Date Commission])*100)

Where they exceed KPI's eg expect to have done no construction, but have
done some, it is throwing out some weird results. eg 2225%. Can anyone
explain to me what is happening here, and how to fix it.

thanks!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Grouping data from different columns 1
Some forms based on queries are uneditable, but queries don't overlap 1
Huge Project and would love some tips 0
Calculating Date and Time using IIf statements - Please Help Me 1
Using Project as a 12 month timekeeping system. 9
Select query driving me mad (using *) 4
table set up 1
Date Question, not sure which forum 2

Top