MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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