Another query

  • Thread starter Thread starter Brian Tozer
  • Start date Start date
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
 
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
 
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
 
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
 
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 ?
 
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)
 
Back
Top