Don't calculate this row

Posted by Roni on August 29, 2001 8:28 AM

I have a spreadsheet that calculates columns and rows of numbers.
However, want to keep track of one certain row of numbers, but I
do NOT want it to add in to the total at the right nor the bottom.

How do I NOT CALCULATE certain cells????

Posted by Aladin Akyurek on August 29, 2001 8:44 AM

By excluding them from your ranges, I'd think.

For example:

If uou want the sum of cells in A2:A10 and A15:A30, you can use:

=SUM(A2:A10,A15:A30)

This excludes as it were A11:A14.

If this is not what you meant, care to elaborate?

Aladin

Posted by Roni on August 29, 2001 9:11 AM

That won't really work, b/c it's the 43rd row in a 263 row spreadsheet,
which is totaled at both the bottom, (each column gets totaled down and across),
and totalled to the right (there are 9 columns and a total for each individual
row in a TOTAL column to the right).

Is this any more clear??? Thanks.

Posted by Aladin Akyurek on August 29, 2001 9:34 AM

Not sure but what about entering in A264

=SUM(A1:A263)-A$43

and copying across and entering in J1

=(ROW()&LT;&GT;43)*(SUM(A1:I1)) [ or: =IF(ROW()&LT;&GT;43,SUM(A1:C1),"") ]

and copying this down up to row 263?

Aladin

Posted by Roni on August 29, 2001 10:12 AM

yes, that's a good idea, the -(a43). (I had changed
that formula to add rows 1-42, and then 44-263.)
one further question, in each/either case, IF I have
to INSERT a new row, or delete one in the future (which
is very probably), will either formula keep it's
alliance with that particular line #. Meaning, there
will be numbers associated with that "client" that I
never want to total, but if that "client" ends up
on line 44, will the formula adjust itself when I
insert of delete a row? Follow?

Posted by IML on August 29, 2001 10:51 AM

Just a thought, is the client you want to exclude listed in the spreadsheet?
You could use a simple sumif, if for example, you want to total column A and the firm name or key to exclude is in column C.

For example:
=SUMIF(C2:C263,"&LT;&GT;abc corp",A2:A263)
would exclude items labeled ABC Corp. This may help with your desire to make it more dymanic.

Good luck.

Posted by Roni on August 29, 2001 11:37 AM

Not understanding??? Can I send an example.

That sounds like a good idea, but I'm not entirely
clear on how to set it up in my spreadsheet. Is
there a way I can send you a small example of the
spreadsheet and then you can explain it again to me?

If it helps, my direct email is rgardner@mdp.com.

thanks again.

Posted by IML on August 29, 2001 1:05 PM

I sent you and example

let me know if that doesn't help clarify... That sounds like a good idea, but I'm not entirely

Posted by Aladin Akyurek on August 29, 2001 2:34 PM

Re: I sent you and example

Ian, Thanks for taking it up. (Was off to a dinner.) let me know if that doesn't help clarify... : That sounds like a good idea, but I'm not entirely

Posted by IML on August 29, 2001 3:21 PM

Re: I sent you and example

Glad to add my two cents, I supposed you do have eat sometime! Since I'm about to take my leave for the night, I assumed a data set in A1:J21. First three rows are:
{"FIRM","Data 1","Data 2","Data 3","Data 4","Data 5","Data 6","Data 7","Data 8","Data 9";"ABC1",100,200,300,400,500,600,700,800,900;"ABC3",100,200,300,400,500,600,700,800,900}

The formula to total columns was
=SUMIF($A$2:$A$21,"&LT;&GT;ABC1",B2:B21)
and to total rows was
=SUM(B2:J2)*(A2&LT;&GT;"ABC1")

I'm just throwing this out there since I always learn more by seeing people improve on my suggestions.

Have a good evening.