A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Calculate every 5th Column.



 
 
Thread Tools Display Modes
  #1  
Old December 15th 06, 02:13 AM posted to microsoft.public.excel.worksheet.functions
Michael
external usenet poster
 
Posts: 18
Default 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
NO VBA please.

Thanks
Ads
  #2  
Old December 15th 06, 02:33 AM posted to microsoft.public.excel.worksheet.functions
Roger Govier
external usenet poster
 
Posts: 2,886
Default 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
> NO VBA please.
>
> Thanks



  #3  
Old December 15th 06, 02:35 AM posted to microsoft.public.excel.worksheet.functions
Max
external usenet poster
 
Posts: 9,221
Default 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
> NO VBA please.
>
> Thanks
>

  #4  
Old December 15th 06, 02:53 AM posted to microsoft.public.excel.worksheet.functions
Michael
external usenet poster
 
Posts: 18
Default 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  
Old December 15th 06, 02:53 AM posted to microsoft.public.excel.worksheet.functions
Michael
external usenet poster
 
Posts: 18
Default 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  
Old December 15th 06, 08:54 AM posted to microsoft.public.excel.worksheet.functions
Max
external usenet poster
 
Posts: 9,221
Default Calculate every 5th Column.

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



  #7  
Old December 16th 06, 01:35 AM posted to microsoft.public.excel.worksheet.functions
Michael
external usenet poster
 
Posts: 18
Default 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  
Old December 18th 06, 01:22 PM posted to microsoft.public.excel.worksheet.functions
Roger Govier
external usenet poster
 
Posts: 2,886
Default 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



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Conditional Format as a MACRO Gunjani Excel Worksheet Functions 3 March 29th 06 05:22 PM
Return SEARCHED Column Number of Numeric Label and Value Sam via OfficeKB.com Excel Worksheet Functions 23 January 30th 06 07:16 PM
Need to Improve Code Copying/Pasting Between Workbooks David Excel Discussion (Misc queries) 1 January 6th 06 04:56 AM
Positioning Numeric Values Resulting from 6 Column Array Formula Sam via OfficeKB.com Excel Worksheet Functions 2 January 5th 06 03:03 AM
Return Count for LAST NonBlank Cell in each Row Sam via OfficeKB.com Excel Worksheet Functions 12 April 17th 05 10:36 PM


All times are GMT +1. The time now is 11:21 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.