Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
refer to cell relative to range | Excel Discussion (Misc queries) | |||
Refer to a Single cell in a name range | Excel Worksheet Functions | |||
Can a cell refer to a Range of cells? | Excel Discussion (Misc queries) | |||
???Refer to a specifc cell in a named range | Excel Worksheet Functions | |||
How do I refer a Range to a Cell | Excel Worksheet Functions |