Nested Funtions
 

Is it possible to add one more argument to this formula?

=IF(COUNT(OFFSET($B138,-4,0,-6,1))=6,AVERAGE(OFFSET(C138,-4,0,-6,1)),"N/A")

I want to specify that if the result of the average is an error, then also
return "N/A"

=IF(COUNT(OFFSET($B18,-4,0,-6,1))=6,IF(NOT(ISERROR(AVERAGE(OFFSET(C18,-4,0,-
6,1)))),AVERAGE(OFFSET(C18,-4,0,-6,1)),NA()),NA())

--
HTH

Bob Phillips

"StephanieH" wrote in message
...
Is it possible to add one more argument to this formula?


=IF(COUNT(OFFSET($B138,-4,0,-6,1))=6,AVERAGE(OFFSET(C138,-4,0,-6,1)),"N/A")

I want to specify that if the result of the average is an error, then also
return "N/A"

=IF(iserror(AVERAGE(OFFSET(C138,-4,0,-6,1))),"N/A",COUNT(OFFSET($B138,-4,0,-
6,1))=6,AVERAGE(OFFSET(C138,-4,0,-6,1)),"N/A")
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com



"StephanieH" wrote in message
...
Is it possible to add one more argument to this formula?


=IF(COUNT(OFFSET($B138,-4,0,-6,1))=6,AVERAGE(OFFSET(C138,-4,0,-6,1)),"N/A")

I want to specify that if the result of the average is an error, then also
return "N/A"

Bob Phillips wrote...
=IF(COUNT(OFFSET($B18,-4,0,-6,1))=6,IF(NOT(ISERROR(AVERAGE(OFFSET(C18,-4,0,
-6,1)))),AVERAGE(OFFSET(C18,-4,0,-6,1)),NA()),NA())

Maybe

=IF(AND(COUNT(OFFSET($B18,-4,0,-6,1))=6,COUNT(OFFSET(C18,-4,0,-6,1))),
AVERAGE(OFFSET(C18,-4,0,-6,1)),"N/A")

However, I have a big problem with negative 4th & 5th arguments to
OFFSET. Makes workbooks much harder to maintain than necessary. Better
to write this as

=IF(AND(COUNT(OFFSET($B18,-9,0,6,1))=6,COUNT(OFFSET(C18,-9,0,6,1))),
AVERAGE(OFFSET(C18,-9,0,6,1)),"N/A")

In general,

OFFSET(rng,ro,co,rc,cc)

could be rewritten as

OFFSET(rng,ro+rc-SIGN(rc),co+cc-SIGN(cc),-rc,-cc) .

I'm not sure I understand the advantage to
OFFSET(rng,ro+rc-SIGN(rc),co+cc-SIGN(cc),-rc,-cc)

We add a row above the formulas each month and insert more data. The offset
takes care of the adjustments were were making each month because of the
inserted row. Since we no longer make adjustments, how would eliminating the
negative help me?



"Harlan Grove" wrote:

Bob Phillips wrote...
=IF(COUNT(OFFSET($B18,-4,0,-6,1))=6,IF(NOT(ISERROR(AVERAGE(OFFSET(C18,-4,0,
-6,1)))),AVERAGE(OFFSET(C18,-4,0,-6,1)),NA()),NA())

Maybe

=IF(AND(COUNT(OFFSET($B18,-4,0,-6,1))=6,COUNT(OFFSET(C18,-4,0,-6,1))),
AVERAGE(OFFSET(C18,-4,0,-6,1)),"N/A")

However, I have a big problem with negative 4th & 5th arguments to
OFFSET. Makes workbooks much harder to maintain than necessary. Better
to write this as

=IF(AND(COUNT(OFFSET($B18,-9,0,6,1))=6,COUNT(OFFSET(C18,-9,0,6,1))),
AVERAGE(OFFSET(C18,-9,0,6,1)),"N/A")

In general,

OFFSET(rng,ro,co,rc,cc)

could be rewritten as

OFFSET(rng,ro+rc-SIGN(rc),co+cc-SIGN(cc),-rc,-cc) .

"Harlan Grove" wrote in message
ups.com...

However, I have a big problem with negative 4th & 5th arguments to
OFFSET. Makes workbooks much harder to maintain than necessary.

Agreed. That was the hardest bit about checking the answer, making sure I
covered the correct range. But it is also true of negative 3rd and 4th.

StephanieH wrote...
I'm not sure I understand the advantage to
OFFSET(rng,ro+rc-SIGN(rc),co+cc-SIGN(cc),-rc,-cc)

Your original formula used function calls like

OFFSET($B18,-4,0,-6,1)

The arguments are hardcoded, so there should be no big deal changing
them to

OFFSET($B18,-9,0,6,1)

The advantage of this for future maintainers is that the 1st, 2nd and
3rd arguments alone establish the starting (top-left) cell, and the 4th
and 5th arguments only determine the size of the range. Using negative
4th or 5th arguments, all arguments are needed to determine the
starting (top-left) cell.

The ending (bottom-right) cell of the range produced by both of these
function calls is B14. That is easier to figure from your function
call. So I guess it's a question of whether one prefers it to be easier
to figure out the top-left or bottom-right cell in a dynamic range, and
that's subjective.

We add a row above the formulas each month and insert more data. The offset
takes care of the adjustments were were making each month because of the
inserted row. Since we no longer make adjustments, how would eliminating the
negative help me?

Aside from the 1st, the arguments in your OFFSET calls are constants.
If you're inserting rows on or above row 18, only the 1st argument
would be adjusted by the row insertion. So if you insert 2 rows above
row 18, your function call above would become

OFFSET($B20,-4,0,-6,1)

which would return B11:B16. On the other hand, my formula would become

OFFSET($B20,-9,0,6,1)

which would also return B11:B16.

It's all a question of whether it should be easier to figure out the
top-left or bottom-right cell in the range. For me, it's usually better
to be able to figure out the top-left cell, but I'll admit that could
be subjective.

Oh I see what you're saying. In this case, the starting point is always the
same row, but our range moves up a row for each column to the right. It
makes it easy to move through the cells and make sure they're following the
logic. But, your point is good to know in cases where it makes more sense to
see where the top of the range is instead. I'll keep it in mind.

Thanks Harlan.



"Harlan Grove" wrote:

StephanieH wrote...
I'm not sure I understand the advantage to
OFFSET(rng,ro+rc-SIGN(rc),co+cc-SIGN(cc),-rc,-cc)

Your original formula used function calls like

OFFSET($B18,-4,0,-6,1)

The arguments are hardcoded, so there should be no big deal changing
them to

OFFSET($B18,-9,0,6,1)

The advantage of this for future maintainers is that the 1st, 2nd and
3rd arguments alone establish the starting (top-left) cell, and the 4th
and 5th arguments only determine the size of the range. Using negative
4th or 5th arguments, all arguments are needed to determine the
starting (top-left) cell.

The ending (bottom-right) cell of the range produced by both of these
function calls is B14. That is easier to figure from your function
call. So I guess it's a question of whether one prefers it to be easier
to figure out the top-left or bottom-right cell in a dynamic range, and
that's subjective.

We add a row above the formulas each month and insert more data. The offset
takes care of the adjustments were were making each month because of the
inserted row. Since we no longer make adjustments, how would eliminating the
negative help me?

Aside from the 1st, the arguments in your OFFSET calls are constants.
If you're inserting rows on or above row 18, only the 1st argument
would be adjusted by the row insertion. So if you insert 2 rows above
row 18, your function call above would become

OFFSET($B20,-4,0,-6,1)

which would return B11:B16. On the other hand, my formula would become

OFFSET($B20,-9,0,6,1)

which would also return B11:B16.

It's all a question of whether it should be easier to figure out the
top-left or bottom-right cell in the range. For me, it's usually better
to be able to figure out the top-left cell, but I'll admit that could
be subjective.