Calculate every 5th Column.

[Michael]

I have a request that has defeated me.


I would like to calculate the maximum value of the 5th consecutive
columns up 95(19*5).

I.e the maximum vale in column E,J,T,Y etc
NO VBA please.

Thanks

 

[Roger Govier]

Hi Michael

Try the array entered formula
{=MAX(IF(MOD(COLUMN(A1:CL1),5)=0,A1:CL1))}

Use Control+Shift+Enter (CSE) to commit or edit the formula rather than
just Enter.
When you use CSE, Excel will insert the curly braces { } for you - do
not type them yourself.

 

[Guest]

Try, array-entered (press CTRL+SHIFT+ENTER):
=MAX(IF(MOD(COLUMN(E1:IV100),5)=0,E1:IV100))

 

[Michael]

Hi Michael

Try the array entered formula
{=MAX(IF(MOD(COLUMN(A1:CL1),5)=0,A1:CL1))}

Use Control+Shift+Enter (CSE) to commit or edit the formula rather than
just Enter.
When you use CSE, Excel will insert the curly braces { } for you - do
not type them yourself.

Thanks Roger.

 

[Michael]

Try, array-entered (press CTRL+SHIFT+ENTER):
=MAX(IF(MOD(COLUMN(E1:IV100),5)=0,E1:IV100))

Thanks Max

 

[Max]

You're welcome, Michael !

 

[Michael]

Thanks Roger.

The above formula worked.
I have another request which is related the original post.
Every 5th column the in ROW 1 in numbered sequentially from 1 to 19.
In rows 3 to 22
Although I initially requested the maximum for the whole row.
I want the max value up to certain points in the but always counting
from the left to any 5th column let's say from 1 to 9.
I hope this makes sense to anyone that can help.

Michael

 

[Roger Govier]

Hi Michael

I do apologise, but I'm not quite following what you are after here.
Could you try explaining again, perhaps with some sort of example, and
then maybe I, or somebody else, will be able to help you.