Function to Calculate a Product
Posted by Mick Curcuruto on June 25, 2000 1:58 AM
I require a function to calulate the product of numbers stored in the same columns, say with the column names, N T L W H.
In many cases one or more of the columns may be blank and different columns could be blank.
For example there could be three numbers one in T and the other two in W and H
Posted by Ivan Moala on June 25, 0100 2:41 AM
Mike
have you checked out the SUMPRODUCT function ??
Look @ the online help for more info.
If this is not what you are after then please
elaborate more and perhaps we can help.
Ivan
Posted by mads on July 04, 0100 5:09 AM
Try this :-
Insert at the start of your function :-
If N + T + L + W + H = 0 Then
Calc = 0
Exit Function
End If
Delete the last line of your function and replace with :-
If N + T + L + W + H <> 0 Then
Calc = N * T * L * W * H
End If
mads
Posted by Mick Curcuruto on June 28, 0100 4:54 PM
In many cases one or more of the columns may be blank and different columns could be blank. For example there could be three numbers one in T and the other two in W and H
Ivan because some of the columns are blank I would end up with a #Value# error.
One of the things I tried was as follows
Function Calc(N,T,L,W,H)
If isnull(N) or N="" then
N=1
End If
If isnull(T) or T="" then
T=1
End If
If isnull(L) or L="" then
L=1
End If
If isnull(W) or W="" then
W=1
End If
If isnull(H) or H="" then
H=1
End If
Calc=N*T*L*W*H
End Function
The only problem is that I end up with 1 (one) as the answer in a row of data which has no entries if I copy the function down
Regards Mick
