Originally Posted by
GCLIFTON
Nope didnt work but this did, but i am still not getting the right number. For instance why is there a 700 and 1.000xxxxxxx etc. This column will be a percentage section in my report
TEST1: IIf([Budget Total]=1,0,CDbl(Nz([Tracker SubTotal],0)/CDbl(Nz([Budget Total]-1))))
--------------
OK, the 700.
Lets say
[Budget Total] = 0 and
[Tracker SubTotal] = -700 (I REALLY hate spaces in names!!)
Putting the values in the formula results in
=IIf([Budget Total]=1,0,CDbl(Nz([Tracker SubTotal],0)/CDbl(Nz([Budget Total]-1))))
=IIf([0]=1,0,CDbl(Nz([-700],0)/CDbl(Nz([0]-1))))
Doing the math results in
=IIf([0]=1,0,CDbl(Nz([-700],0)/CDbl(-1))))
which is
=CDbl(Nz([-700],0)/CDbl(-1)))
getting rid of the un-necessary
= -700/-1
which equals 700.
--------------
Now the 1.0001542738151
[Budget Total] = 6482.9814 and
[Tracker SubTotal] = 6482.9814
Putting the values in the formula results in
=IIf([Budget Total]=1,0,CDbl(Nz([Tracker SubTotal],0)/CDbl(Nz([Budget Total]-1))))
=IIf([0]=1,0,CDbl(Nz([6482.9814],0)/CDbl(Nz([6482.9814]-1))))
Doing the math results in
=IIf([0]=1,0,CDbl(Nz([6482.9814],0)/CDbl(6481.9814))))
which is
=CDbl(Nz([6482.9814],0)/CDbl(6481.9814)))
getting rid of the un-necessary
= 6482.9814 / 6481.9814
which equals 1.0001542738151
For the 700 issue, maybe try
Code:
TEST1: IIf([Budget Total]=0 or [Budget Total]=1,0,CDbl(Nz([Tracker SubTotal],0)/CDbl(Nz([Budget Total]-1))))