How to refer to a range except one cell
 

Hi,

I'm trying to average a range except one cell in the middle of that range
(or alternatively to condition on a range made of two separate sections). The
formula I'm trying to use is the following:

=IF(AND(B$470, B$53=2), B$47-AVERAGEIF(B$45:B$50, "0"), "-")

Except, instead of B45:B50 in AVERAGEIF, I want the range to be B45:B46 and
B48:B50 (i.e. without the cell B47).
Can anyone tell me how to use a split range with AVERAGEIF or how to tell it
not to look at cell B47?

Grateful for any advice. Thanks!

How to refer to a range except one cell
 

Just use some helper cells:
in Z45, enter =B45
in Z46, enter =B46
in Z48, enter =B48
in Z49, enter =B49
in Z50, enter =B50

leave Z47 blank

then:

=IF(AND(B$470, B$53=2), B$47-AVERAGEIF(Z$45:Z$50, "0"), "-")


--
Gary''s Student - gsnu200909


"hs" wrote:

Hi,

I'm trying to average a range except one cell in the middle of that range
(or alternatively to condition on a range made of two separate sections). The
formula I'm trying to use is the following:

=IF(AND(B$470, B$53=2), B$47-AVERAGEIF(B$45:B$50, "0"), "-")

Except, instead of B45:B50 in AVERAGEIF, I want the range to be B45:B46 and
B48:B50 (i.e. without the cell B47).
Can anyone tell me how to use a split range with AVERAGEIF or how to tell it
not to look at cell B47?

Grateful for any advice. Thanks!

How to refer to a range except one cell
 

I need this formula to change for a different cell every time, so helper
cells will make the worksheet really messy and I would need to put them in
for every different case. It'd be much easier if I could put it into a
formula. Is there no way to tell excel to not look at a cell if it is a
specific cell?


"Gary''s Student" wrote:

Just use some helper cells:
in Z45, enter =B45
in Z46, enter =B46
in Z48, enter =B48
in Z49, enter =B49
in Z50, enter =B50

leave Z47 blank

then:

=IF(AND(B$470, B$53=2), B$47-AVERAGEIF(Z$45:Z$50, "0"), "-")


--
Gary''s Student - gsnu200909


"hs" wrote:

Hi,

I'm trying to average a range except one cell in the middle of that range
(or alternatively to condition on a range made of two separate sections). The
formula I'm trying to use is the following:

=IF(AND(B$470, B$53=2), B$47-AVERAGEIF(B$45:B$50, "0"), "-")

Except, instead of B45:B50 in AVERAGEIF, I want the range to be B45:B46 and
B48:B50 (i.e. without the cell B47).
Can anyone tell me how to use a split range with AVERAGEIF or how to tell it
not to look at cell B47?

Grateful for any advice. Thanks!

How to refer to a range except one cell
 

hs wrote:
I need this formula to change for a different cell every time, so helper
cells will make the worksheet really messy and I would need to put them in
for every different case. It'd be much easier if I could put it into a
formula. Is there no way to tell excel to not look at a cell if it is a
specific cell?


Go back and rethink your worksheet logic. First you say you need the
formula to change for a different cell every time, then you ask if there
is no way to tell excel to not look at a specific cell. Make up your mind.

Bill

How to refer to a range except one cell
 

My worksheet logic is fine. The cell I need it to skip changes depending on
which rubric I'm in in the worksheet.



"Bill Sharpe" wrote:

hs wrote:
I need this formula to change for a different cell every time, so helper
cells will make the worksheet really messy and I would need to put them in
for every different case. It'd be much easier if I could put it into a
formula. Is there no way to tell excel to not look at a cell if it is a
specific cell?


Go back and rethink your worksheet logic. First you say you need the
formula to change for a different cell every time, then you ask if there
is no way to tell excel to not look at a specific cell. Make up your mind.

Bill
.