Remember Me?

#1
December 15th 06, 02:13 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Nov 2006 Posts: 18
Calculate every 5th Column.

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

Thanks

#2
December 15th 06, 02:33 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 2,886
Calculate every 5th Column.

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.
--
Regards

Roger Govier

"Michael" wrote in message
...
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

Thanks

#3
December 15th 06, 02:35 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 9,221
Calculate every 5th Column.

Try, array-entered (press CTRL+SHIFT+ENTER):
=MAX(IF(MOD(COLUMN(E1:IV100),5)=0,E1:IV100))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Michael" wrote:
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

Thanks

#4
December 15th 06, 02:53 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Nov 2006 Posts: 18
Calculate every 5th Column.

On Fri, 15 Dec 2006 01:33:58 -0000, "Roger Govier"
wrote:

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.
#5
December 15th 06, 02:53 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Nov 2006 Posts: 18
Calculate every 5th Column.

On Thu, 14 Dec 2006 17:35:00 -0800, Max wrote:

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

Thanks Max

#6
December 15th 06, 08:54 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 9,221
Calculate every 5th Column.

You're welcome, Michael !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Michael" wrote in message
...
.. Thanks Max

#7
December 16th 06, 01:35 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Nov 2006 Posts: 18
Calculate every 5th Column.

On Fri, 15 Dec 2006 01:53:06 +0000, Michael
wrote:

On Fri, 15 Dec 2006 01:33:58 -0000, "Roger Govier"
wrote:

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.

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
#8
December 18th 06, 01:22 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 2,886
Calculate every 5th Column.

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.

--
Regards

Roger Govier

"Michael" wrote in message
...
On Fri, 15 Dec 2006 01:53:06 +0000, Michael
wrote:

On Fri, 15 Dec 2006 01:33:58 -0000, "Roger Govier"
wrote:

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.

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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Gunjani Excel Worksheet Functions 3 March 29th 06 05:22 PM Sam via OfficeKB.com Excel Worksheet Functions 23 January 30th 06 07:16 PM David Excel Discussion (Misc queries) 1 January 6th 06 04:56 AM Sam via OfficeKB.com Excel Worksheet Functions 2 January 5th 06 03:03 AM Sam via OfficeKB.com Excel Worksheet Functions 12 April 17th 05 10:36 PM

All times are GMT +1. The time now is 09:10 PM.