Calculations with null fields

[Guest]

Hello, I have a number of text boxes in my report with % investment return
fields, ie Jan02, Feb02 etc. Not all of my record have a % return for a
particular month.

I now want to calculate the cummulative return for a period and am using a
text box with the following formula in:

=((IIf(IsNull([Jan02]),nz(Jan02]),[Jan02]+1))*(IIf(IsNull([Feb02]),nz([Feb02]),[Feb02]+1)). . .

I am looking for a more efficient way to do this - any suggestions?

Thanks
Tarryn

 

[Andi Mayer]

Hello, I have a number of text boxes in my report with % investment return
fields, ie Jan02, Feb02 etc. Not all of my record have a % return for a
particular month.

I now want to calculate the cummulative return for a period and am using a
text box with the following formula in:

=((IIf(IsNull([Jan02]),nz(Jan02]),[Jan02]+1))*(IIf(IsNull([Feb02]),nz([Feb02]),[Feb02]+1)). . .

I am looking for a more efficient way to do this - any suggestions?

the nz function is looking for NULL and if this Value is Null it get's
translated to 0 with numeric Values.

Actually it is Nz( Value, translateValue)

you are checking your values two times the same

=(Nz(Jan02,-1)+1)*(Nz(Feb02,-1)+1)

if the Value is NULL it get's translated to -1 and then I add 1 and
the result is 0
else the Value get added 1

 

[Guest]

Why add and subtract 1? Try Nz([Jan02],0)+Nz([Feb02],0)+ etc.
Otherwise to make it shorter:
Nz([Jan02])+Nz([Feb02])+ etc and then add 0 to the end of the equation in
case all are null.

Hope this helps.
Jackie )

Hello, I have a number of text boxes in my report with % investment return
fields, ie Jan02, Feb02 etc. Not all of my record have a % return for a
particular month.

I now want to calculate the cummulative return for a period and am using a
text box with the following formula in:

=((IIf(IsNull([Jan02]),nz(Jan02]),[Jan02]+1))*(IIf(IsNull([Feb02]),nz([Feb02]),[Feb02]+1)). . .

I am looking for a more efficient way to do this - any suggestions?

the nz function is looking for NULL and if this Value is Null it get's
translated to 0 with numeric Values.

Actually it is Nz( Value, translateValue)

you are checking your values two times the same

=(Nz(Jan02,-1)+1)*(Nz(Feb02,-1)+1)

if the Value is NULL it get's translated to -1 and then I add 1 and
the result is 0
else the Value get added 1

 

[Andi Mayer]

Why add and subtract 1? Try Nz([Jan02],0)+Nz([Feb02],0)+ etc.

because the oringinal added 1 if not null

=((IIf(IsNull([Jan02]),nz(Jan02]),[Jan02]+1))*(IIf(IsNull([Feb02]),nz([Feb02]),[Feb02]+1)). . .

 

[Guest]

Hi, thanks for the response -

Just want to clarify, the add 1 is because I am working out the compound
return from an investment : compound return = (returnA% +1)*(returnB%+1) - 1

Basically, I want to ignore those returns that are null and add 1 to those
returns that are not and then find the product of those to give me a compound
return.

Thanks
Tarryn

Why add and subtract 1? Try Nz([Jan02],0)+Nz([Feb02],0)+ etc.

because the oringinal added 1 if not null

=((IIf(IsNull([Jan02]),nz(Jan02]),[Jan02]+1))*(IIf(IsNull([Feb02]),nz([Feb02]),[Feb02]+1)). . .