Eliminating a Column from Formula

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

Is there a way to eliminate a specific column or columns from any
formula ie: sum, average, count etc. when the formula includes all
columns

In other words if the formula is =sum(A1:H1) I would like to eliminate
column D from the formula so the end result would be only to sum
columns A to C and E to H. The formulas would have an appearance like
=sum(a1:h1-d1)

Could the elimination of a column also be accomplished in more complex
formulas when arrays are used?
 
Hi

with your first question
=SUM(A1:H1)-D1
would work
as for eliminating ranges in array formulas - AFAIK, not so easy, but if you
give us an example of what you're after i'm pretty sure someone will be able
to give a useful answer
 
Thanks for your answer but how would this work for other functions ie
average, count, max, min etc?

Tom
 
<with your first question
=SUM(A1:H1)-D1
would work>

Unless D1 contains an error value, such as #VALUE.
You can also use =SUM(A1:C1,E1:H1)

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel
 
<You can also use =SUM(A1:C1,E1:H1)>

This method would apply to many functions and I think to all the ones you
mentioned.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel
 
You could include the ability to enter the cell to exclude into another
cell.

For example, if you were adding your row (Row1), with the total in I1, and
you wanted the option to eliminate an optional column, you could stipulate
that the cell to be removed from the sum formula could be entered in K1, so:

=IF(K1<>"",SUM(A1:H1,-INDIRECT(K1)),SUM(A1:H1))

To eliminate D1 from the total, enter "D1" into K1 (no quotes).

To carry this a little further, if you were to copy this formula down Column
I, where you might want to exclude various Columns from different row
totals, you could try this formula, where you just enter the name of the
Column to eliminate into the corresponding row in Column K:

=IF(K1<>"",SUM(A1:H1,-INDIRECT(K1&ROW())),SUM(A1:H1))
 
Tom said:
Is there a way to eliminate a specific column or columns from any
formula ie: sum, average, count etc. when the formula includes all
columns

In other words if the formula is =sum(A1:H1) I would like to eliminate
column D from the formula so the end result would be only to sum
columns A to C and E to H. The formulas would have an appearance like
=sum(a1:h1-d1)

Could the elimination of a column also be accomplished in more complex
formulas when arrays are used?
Click to expand...

=SUM(A1:H1,-D1) for a single row. Array entered for a multi-row range;
e.g., {=SUM(A1:H10,-D1:D10)} (don't enter the braces, just array enter)

Alan Beban
 
Back
Top