Calculation not working due to "0"

[Thomntn]

I work in a grant record keeping office and my boss is ADAMANT this must work. We MUST be able to have access calculate the percentage of awarded grants compared to submitted.

I have form that I need to calculate the percentage of successful grant applications. It works fine EXCEPT for when ALL OF the "FYAwarded" fields are zero. How do I get it to calculate by either bypassing that field when zero or ignoring the field when zero? Here is the calculation:

=([FY2003 awarded]+[fy2004 awarded]+[fy2005 awarded]+[fy2006 awarded]+[fy2007 awarded]+[fy2008 awarded]+[fy2009 awarded]+[fy2010 awarded])/([FY2003 Submitted]+[fy2004 submitted]+[fy2005 submitted]+[fy2006 submitted]+[fy2007 submitted]+[fy2008 submitted]+[fy2009 submitted]+[fy2010 submitted])-([FY2004 Pending]+[FY 2005 Pending]+[FY2006 Pending]+[FY2007 Pending]+[FY2008 Pending]+[FY2009 Pending]+[fy2010 pending])*100

To help a lil with the lingo:

All grants are recorded as submitted. That number constantly increases. They are then placed in the "pending" number also. As grants are awarded or unfunded the pending/awarded/unfunded change but SUBMITTED never changes except to add more to.

Anyone able to help?

 

[PaulJR]

I'm not too sure what you're looking for, you say it works fine except - what goes wrong? I have simplified your formula to:

(A1+B1)/(A2-B2)*100.

and I wondered if something like this may help?

=IIf((A1+B1)>0,(A1+B1)/(A2-B2)*100,0)

I'm not sure if you need to google the iif statement to see how it works.

 

[missinglinq]

Is the problem happening when the fields are Zero, or when they're Null or empty? Adding a Null value to anything gives you a Null value as a result. To prevent this, use the Nz() function to change Nulls to Zero:

Nz([fy2009 awarded] , 0) + Nz([fy2010 awarded], 0) ... etc.