Eliminating a Column from Formula

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

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Tom" <[email protected]> wrote in message
news:[email protected]...
> 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?
>

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

"JulieD" <[email protected]> wrote in message
news:%[email protected]...
> 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
>
> --
> Cheers
> JulieD
> check out www.hcts.net.au/tipsandtricks.htm
> ...well i'm working on it anyway
> "Tom" <[email protected]> wrote in message
> news:[email protected]...
>> 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?
>>
>
>

<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

"Niek Otten" <[email protected]> wrote in message
news:u6irGB%[email protected]...
> <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
>
> "JulieD" <[email protected]> wrote in message
> news:%[email protected]...
>> 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
>>
>> --
>> Cheers
>> JulieD
>> check out www.hcts.net.au/tipsandtricks.htm
>> ...well i'm working on it anyway
>> "Tom" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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?
>>>
>>
>>
>
>

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))
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------



"Tom" <[email protected]> wrote in message
news:[email protected]...
> Thanks for your answer but how would this work for other functions ie
> average, count, max, min etc?
>
> Tom
>

Tom wrote:
> 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?
>

=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