Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate every 5th Column.
You're welcome, Michael !
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Michael" wrote in message ... .. Thanks Max |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Format as a MACRO | Excel Worksheet Functions | |||
Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions |