ISODD() is odd

[N1GHTEYES]

I recently used an array formula in a workbook where I wanted to find the mean of all the values in the range "data" which lay on the odd-numbered rows.

The formula I used was:
={average(IF(ISODD(ROW(data)),data))}
This gave a VALUE! error.

However,
={average(IF(ROW(data)=ODD(ROW(data)),data))}
worked fine.

Any idea why?

Tony

 

[SkipVought]

hi,

Try it WITHOUT array key seq
[tt]
=AVERAGE(IF(ISODD(ROW()),data))
[/tt]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

sorry, the original formula as an array seems to work for me
[tt]
={AVERAGE(IF(ISODD(ROW(data)),data))}
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[N1GHTEYES]

If you do that then it returns one of two possible outcomes (I tried it):

If data starts on an odd row, it gives the average of the whole of data, otherwise it returns zero. I think that is because in the expression:
=AVERAGE(IF(ISODD(ROW(data)),data))
when it is entered without being an array formula, one check only is done on ISODD(ROW(data)) and what it returns is whether the first row of data is odd or not.

Tony

 

[N1GHTEYES]

Sorry Skip, we seem to have overlapped. My response was to your first post.

In response to your second post, I really do get the #VALUE! error. Could this be an office-version-dependent issue? I am using 2003, SP3. How about you?

Tony

 

[SkipVought]

2007

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Rofeu]

In office 2003 the ISODD formula is non-standard and is not suited for use in an array-formula. In 2007 I believe it has been made part of the standard and can be used in array-formulae.

Cheers,

Rofeu

 

[N1GHTEYES]

Oh, that would explain it then. It's nice to know I'm not just being stupid.

For info, if anyone else has issues with it, note that you can do what I mentioned in my first post, i.e. replace (ISODD(range)) with (range=ODD(range)). That does work in array formulae.

Tony

 

[lionelhill]

If you're in 2003 and (like many of us who lack brain-cells) don't feel you understand array formulae, there are several other options.

My favourite: assuming the data are in column A,
B1=A1
B2=A2
B3=A3+B1
Copy formula in B3 to the end of the data.
The last two entries in column B are the sums of all odd and even rows, and can be divided by an appropriately rounded count of total rows (round up) to find the mean.

This approach has one useful feature: you can easily modify it to average every 3rd, 7th or 13th row if you so wish.

 

[N1GHTEYES]

Thanks Lionel. I could use a "helper column" as you suggest, and it would have the benefit of being flexible wrt the base (i.e. not just 2 bu 3, 4 or any number I want), but I really wanted to avoid putting enything into the sheet which did not need to be there.

I am pulling in user-selected data from some data files and was trying to extract all of the information I required by means of suitable array formulae, partly to keep the spreadsheet as simple and uncluttered as possible, but mostly for speed.

I think I've more or less got my head around array formulae, but was puzzled as to why this one did not work. The answer seems to be what Rofeu suggested, i.e. the ISODD function simply does not support array formulae.

Tony

 

[Rofeu]

If you want to use an array-formula and be flexible wrt the base you can use (MOD(COLUMN(<ref>)+<offset>,<ColDif>)=0) instead of ROW(data)=ODD(ROW(data)), where <ColDif> is the number of columns per which you want to check and <offset> the column you want with respect to the base, where <offset> is smaller then <ColDif>.

(obviously you can use it for rows by replacing the COLUMN for ROW)

Cheers,

Rofeu

 

[N1GHTEYES]

Also true and a nice thought. I'll bear it in mind for when I need to use it. For now just Odd & Even rows will do for me.

BTW, I know the ISEVEN() function also does not support array formulae - do you know of any more? Is there a list anywhere?

Tony

 

[SkipVought]

Are you stating, without reservation, that ISEVEN() is odd too??? ;-)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[N1GHTEYES]

Yes, oddly enough, even ISEVEN() is odd.

Tony

 

[bluedragon2]

ISEVEN() is only odd when it says zero which oddly enough is not even odd or even...

OK, enough of this odd behavior

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[kjv1611]

Am I in Making an Impression or Microsoft Office?


"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[SkipVought]

Maybe your are Making an Impression IN Microsoft Office.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]