Another query

B

Brian Tozer

I have a 5 row column C where the formula is A1*B1 in C1, through to A5*B5
in C5.
At present if there is no data in any of the A or B cells the result is
#N/A.
The formula that is used to total the C rows is SUM(C1:C5).
This results in it producing #N/A if any data is missing.
How should the formulas be changed to solve this?

Thanks
Brian Tozer
 
C

count

Hi,
This happens when spaces appear in cells. Try to delete contents of 'empty'
cells in A1:B5.
If pain persists, let us know - :) - some error checking can also be used in
col C.
HTH
Paul
 
B

Brian Tozer

count said:
Hi,
This happens when spaces appear in cells. Try to delete contents of
'empty' cells in A1:B5.
If pain persists, let us know - :) - some error checking can also be
used in col C.
HTH
Paul
Click to expand...

Thanks for the reply Paul.
The space situation is not the problem here.
I simplified my query to eliminate complexity, but I think it was still a
valid description.
So, as I suspected, some error-checking is called for.
As I am a newbie can you make a suggestion or give me a pointer in the right
direction for me to do further research?

Thanks
Brian Tozer
 
C

count

Brian,
Error checking goes like this: put it in C1 (later fill down to C5)
=IF(ISERROR(a1*b1),0,a1*b1)
this should put zero in col C where N/A used to show; hence the total will
be cured.
Look up help for Error Checking - many options exist, like ISNA
Beep if you need help with If structure :)
HTH
Paul
 
R

Ragdyer

I believe you're leaving something out of your description.
If A1 and B1 are blank and C1 contains the formula:
=A1*B1
The result of the formula is 0, not #N/A !

What does A1 and B1 contain when you say that there is no data ?
 
B

Bob Phillips

Brian,

I can't get a #NA. Give us an example of the values in A1 & B1 that cause C1
to have #NA.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Top