Amending a Formula

[Blake]

I have the following formula and it works fine.

=AVERAGE(OFFSET($AH$14,COUNT($AH14:$AH5001)-1,,-14))

I want to amend this formula so that when I have O/A (Off/Average) in
column V it ignores that line as if it wasn't there and omits it from
the averages.

Thank you

 

[joeu2004]

I have the following formula and it works fine.
=AVERAGE(OFFSET($AH$14,COUNT($AH14:$AH5001)-1,,-14))

I don't believe that. As written, you have a height of -14. But
height must positive.

Do you mean 14 instead of -14? Or did you mean "-1,-14" instead of
"-1,,-14"?

(For the future, it is best to copy-and-paste formulas from the
Formula Bar.)

I want to amend this formula so that when I have O/A
(Off/Average) in column V

How does column V factor into this?

If you meant to write "-1,-14", OFFSET returns the address of a single
cell column T, not column V. (And in that case, it would be better to
use INDEX, since that is non-volatile.)

If you meant to write "-1,,14", OFFSET returns a range in column AH.

it ignores that line as if
it wasn't there and omits it from the averages.

Note that AVERAGE automagically ignores empty cells (no constant, no
formula). If also ignores cells that contain text, such as the null
string ("") that makes a cell __appear__ blank, but it is not truly
empty.

If that is all you are worried about, there may be nothing you need to
do.

If these comments do not help, I suggest that you provide a concrete
example where your formula fails to give you what you want. Tell us
what it returns and what you want it to return. And of course, show
us the correct formula ;-).

 

[isabelle]

hi Blake,

named rng1
=OFFSET(Feuil1!$AH$14,,,COUNTA(Feuil1!$AH$14:$AH$5000))

and rng2
=OFFSET(Feuil1!$V$14,,,COUNTA(Feuil1!$V$14:$V$5000))


=SUMIF(rng2,"<>O/A",rng1)/COUNTIF(rng2,"<>O/A")

 

[Blake]

If these comments do not help, I suggest that you provide a concrete
example where your formula fails to give you what you want.  Tell us
what it returns and what you want it to return.  And of course, show
us the correct formula ;-).

I did provide the correct formula. In fact I got it from someone
here.

=AVERAGE(OFFSET($AH$14,COUNT($AH14:$AH5001)-1,,-14))

Example:

A B
4
6
3 O/A
2
5
4 O/A
2


The current formula averages the last 14 days regardless of what is in
column B. What I want to do now is average the last 14 days unless an
O/A appears in column B. In that case it would skip (or throw out)
that line as if it didn't exist. (Columns are different on my
spreadsheet; this is just an example.)

 

[Ken Mintz]

I did provide the correct formula.  In fact I got it
from someone here.
=AVERAGE(OFFSET($AH$14,COUNT($AH14:$AH5001)-1,,-14))

That does not make it right. I am sure you can read the OFFSET help
page just as well as I can. It states: "Height must be a positive
number".

However, I always warn people that we cannot trust MS documentation.
I should have tried your formula myself and pointed out the
discrepancy between MS documentation and actual usage, instead of
taking the MS documentation for granted.

Although it is likely that the support of negative height is an
intentional feature, I try to avoid undocumented features if it is
just as easy not to rely on them. So I would use the following
formula:

=AVERAGE(OFFSET($AH$14,COUNT($AH14:$AH5001)-14,,14))

Example:
A   B
4
6
3   O/A
2
5
4   O/A
2

The current formula averages the last 14 days regardless
of what is in column B.  What I want to do now is average
the last 14 days unless an O/A appears in column B.  In
that case it would skip (or throw out) that line as if it
didn't exist.

Perhaps the following __array_formula__ [*]:

=AVERAGE(IF(OFFSET($B$14,COUNT($A14:$A5001)-14,,14))<>"o/a",
OFFSET($A$14,COUNT($A14:$A5001)-14,,14)))

But your requirements are ambiguously, IMHO.

That formula takes the last 14 non-empty cells in A14:A5001 and
excludes from the average any cells in those 14 that have "O/A" in the
corresponding cell in column B. So you might average fewer than 14
cells.

(In fact, you might average zero cells, in which case that formula
returns an Excel error. If you need help to avoid that error, the
ease of doing so depends on the version of Excel you are using. Which
one?)

That formula does __not__ average the last 14 non-empty cells in
A14:A5001 that do not have "O/A" in the corresponding cell in column
B. In other words, it does __not__ ensure that you average exactly 14
cells.

Which do you require?

To be clear, here is one that you might present your expectations
unambiguously.

Suppose A14:A100 contains numbers. The last 14 non-empty cells are
A87:A100. Suppose that only B88 and B95 contain "O/A".

Do you want effectively AVERAGE(A87,A89:A94,A96:A100)?

Or do you want effectively AVERAGE(A85:A87,A89:A94,A96:A100)?

The latter extends the "range" of cells to be averaged upward to
compensate for the cells that are ignored because of "O/A" in column
B. That is hard, IMHO.

 

[KenM]

I did provide the correct formula. In fact I got it
from someone here.
=AVERAGE(OFFSET($AH$14,COUNT($AH14:$AH5001)-1,,-14))

That does not make it right. As the OFFSET help page states: "Height
must be a positive
number".

However, I always warn people that we cannot trust MS documentation.

It is likely that the support of negative height is an
intentional feature. Nevertheless, I try to avoid undocumented
features if it is just as easy not to rely on them. So I would use
the following formula:

=AVERAGE(OFFSET($AH$14,COUNT($AH14:$AH5001)-14,,14))

Example:
A B
4
6
3 O/A
2
5
4 O/A
2

The current formula averages the last 14 days regardless
of what is in column B. What I want to do now is average
the last 14 days unless an O/A appears in column B. In
that case it would skip (or throw out) that line as if it
didn't exist.

Perhaps the following __array_formula__ [*]:

=AVERAGE(IF(OFFSET($B$14,COUNT($A14:$A5001)-14,,14))<>"o/a",
OFFSET($A$14,COUNT($A14:$A5001)-14,,14)))

[*] Enter an array formula by pressing ctrl+shift+Enter instead of
Enter. Excel will display an array formula surrounded by curly braces
in the Formula Bar, i.e. {=formula}. You cannot type the curly braces
yourself. If you make a mistake, select the cell, press F2 and edit,
then press ctrl+shift+Enter.

But your requirements are ambiguously, IMHO.

That formula takes the last 14 non-empty cells in A14:A5001 and
excludes from the average any cells in those 14 that have "O/A" in the
corresponding cell in column B. So you might average fewer than 14
cells.

(In fact, you might average zero cells, in which case that formula
returns an Excel error. If you need help to avoid that error, the
ease of doing so depends on the version of Excel you are using. Which
one?)

That formula does __not__ average the last 14 non-empty cells in
A14:A5001 that do not have "O/A" in the corresponding cell in column
B. In other words, it does __not__ ensure that you average exactly 14
cells.

Which do you require?

To be clear, here is one that you might present your expectations
unambiguously.

Suppose A14:A100 contains numbers. The last 14 non-empty cells are
A87:A100. Suppose that only B88 and B95 contain "O/A".

Do you want effectively AVERAGE(A87,A89:A94,A96:A100)?

Or do you want effectively AVERAGE(A85:A87,A89:A94,A96:A100)?

The latter extends the "range" of cells to be averaged upward to
compensate for the cells that are ignored because of "O/A" in column
B. That is hard, IMHO.

 

[Ron Rosenfeld]

I don't believe that. As written, you have a height of -14. But
height must positive.

You are, of course, correct according to the documentation.

However, and I think this goes back at least to XL98, the OFFSET function has been accepting negative values for both height and width; and the result has been to affect the direction for determining the height or width.

In addition, just to show how MS handles this, there are Knowledge Base articles indicating that this behavior is incorrect! So, although useful, it may not be supported in the future.

Negative values for height and width seem to work OK in XL 2007 SP1 also.

 

[Ron Rosenfeld]

You are, of course, correct according to the documentation.

However, and I think this goes back at least to XL98, the OFFSET function has been accepting negative values for both height and width; and the result has been to affect the direction for determining the height or width.

In addition, just to show how MS handles this, there are Knowledge Base articles indicating that this behavior is incorrect! So, although useful, it may not be supported in the future.

Negative values for height and width seem to work OK in XL 2007 SP1 also.

Perusing some MSKB articles, I see this issue applies as far back as Excel 5.0 !!

I don't know if it is present in Excel 2010.