Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find out average even rows in a particular range
hi can some one help me to know
i would like find out an aveage from A1:A500 (alternate rows only to say like 1,3,5,7 and so on untill 500th row) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find out average even rows in a particular range
"Muralikrishnan" wrote:
i would like find out an aveage from A1:A500 (alternate rows only to say like 1,3,5,7 and so on until 500th row) Put in say, B1, array-enter the formula by pressing CTRL+SHIFT+ENTER, instead of just pressing ENTER: =AVERAGE(IF(MOD(A1:A500,2),A1:A500)) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find out average even rows in a particular range
hi max
thanks for your e-mail. i have tried it as below: but it appear error. =AVERAGE(IF(MOD(P8:P235,2),P8:P235)) i have in 8 th row % 9th row values, 10th row %, 11th row values like that until 235 row. can u pls reply. ---------------------------------- "Max" wrote: "Muralikrishnan" wrote: i would like find out an aveage from A1:A500 (alternate rows only to say like 1,3,5,7 and so on until 500th row) Put in say, B1, array-enter the formula by pressing CTRL+SHIFT+ENTER, instead of just pressing ENTER: =AVERAGE(IF(MOD(A1:A500,2),A1:A500)) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find out average even rows in a particular range
Sorry, the earlier was wrong
You could try, array-entered in say, Q8: =AVERAGE(IF((MOD(ROW(P8:P235),2)=0)*(P8:P235<""), P8:P235)) to derive average of the 8th, 10th, 12th, etc rows in col P Conversely, this, array-entered: =AVERAGE(IF((MOD(ROW(P8:P235),2)=1)*(P8:P235<""), P8:P235)) will yield the average of the 9th, 11th, 13th, etc rows in col P -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Muralikrishnan" wrote: hi max thanks for your e-mail. i have tried it as below: but it appear error. =AVERAGE(IF(MOD(P8:P235,2),P8:P235)) i have in 8 th row % 9th row values, 10th row %, 11th row values like that until 235 row. can u pls reply. ---------------------------------- |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find out average even rows in a particular range
hi max - again it shows error (value#)
the following data i have in excel : salary : 8th row - 2% 9th row - 2,600 10th - 3% 11th row - 3,315 12th 402 - 4% 13th row - 7,306 14th row - 5% 15th row - 1,680 16th row - 6% 17th row - 3,276 like that until 235 row (some employee row may not have figures for the salary increase) here i have to find what is the average % salary for the given employees. \can u pls reply. "Max" wrote: Sorry, the earlier was wrong You could try, array-entered in say, Q8: =AVERAGE(IF((MOD(ROW(P8:P235),2)=0)*(P8:P235<""), P8:P235)) to derive average of the 8th, 10th, 12th, etc rows in col P Conversely, this, array-entered: =AVERAGE(IF((MOD(ROW(P8:P235),2)=1)*(P8:P235<""), P8:P235)) will yield the average of the 9th, 11th, 13th, etc rows in col P -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Muralikrishnan" wrote: hi max thanks for your e-mail. i have tried it as below: but it appear error. =AVERAGE(IF(MOD(P8:P235,2),P8:P235)) i have in 8 th row % 9th row values, 10th row %, 11th row values like that until 235 row. can u pls reply. ---------------------------------- |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find out average even rows in a particular range
Here's a working sample for easy reference:
http://www.freefilehosting.net/download/3ce95 Average alternating rows.xls -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find out average even rows in a particular range
max-
great. superb... first the same reply no values. second time i read ur lines ...and applied ctrl shift enter... now the % is coming. great superb. my work has be come so much so much easier. otherwise i am gone case. thank you very much muralikrishnan "Max" wrote: Here's a working sample for easy reference: http://www.freefilehosting.net/download/3ce95 Average alternating rows.xls -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find out average even rows in a particular range
Welcome, Muralikrishnan.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Muralikrishnan" wrote in message ... max- great. superb... first the same reply no values. second time i read ur lines ...and applied ctrl shift enter... now the % is coming. great superb. my work has be come so much so much easier. otherwise i am gone case. thank you very much muralikrishnan |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find out average even rows in a particular range
Hi Murali,
You may be able to use a helper column like this. If column Q is not blank insert a new one. Then in Q8 put =P8 Then highlight both P8 and P9 Grab the fill handle and drag down to the end of your data Then just average column Q. HTH Martin "Muralikrishnan" wrote in message ... hi max - again it shows error (value#) the following data i have in excel : salary : 8th row - 2% 9th row - 2,600 10th - 3% 11th row - 3,315 12th 402 - 4% 13th row - 7,306 14th row - 5% 15th row - 1,680 16th row - 6% 17th row - 3,276 like that until 235 row (some employee row may not have figures for the salary increase) here i have to find what is the average % salary for the given employees. \can u pls reply. "Max" wrote: Sorry, the earlier was wrong You could try, array-entered in say, Q8: =AVERAGE(IF((MOD(ROW(P8:P235),2)=0)*(P8:P235<""), P8:P235)) to derive average of the 8th, 10th, 12th, etc rows in col P Conversely, this, array-entered: =AVERAGE(IF((MOD(ROW(P8:P235),2)=1)*(P8:P235<""), P8:P235)) will yield the average of the 9th, 11th, 13th, etc rows in col P -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Muralikrishnan" wrote: hi max thanks for your e-mail. i have tried it as below: but it appear error. =AVERAGE(IF(MOD(P8:P235,2),P8:P235)) i have in 8 th row % 9th row values, 10th row %, 11th row values like that until 235 row. can u pls reply. ---------------------------------- |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find out average even rows in a particular range
martin - i tried & solved the issue.
thank you for the suggestion. honestly i feel, this kind of discussion solves many issues. i enjoyed it yesterday. this is the first time i am using it. "MartinW" wrote: Hi Murali, You may be able to use a helper column like this. If column Q is not blank insert a new one. Then in Q8 put =P8 Then highlight both P8 and P9 Grab the fill handle and drag down to the end of your data Then just average column Q. HTH Martin "Muralikrishnan" wrote in message ... hi max - again it shows error (value#) the following data i have in excel : salary : 8th row - 2% 9th row - 2,600 10th - 3% 11th row - 3,315 12th 402 - 4% 13th row - 7,306 14th row - 5% 15th row - 1,680 16th row - 6% 17th row - 3,276 like that until 235 row (some employee row may not have figures for the salary increase) here i have to find what is the average % salary for the given employees. \can u pls reply. "Max" wrote: Sorry, the earlier was wrong You could try, array-entered in say, Q8: =AVERAGE(IF((MOD(ROW(P8:P235),2)=0)*(P8:P235<""), P8:P235)) to derive average of the 8th, 10th, 12th, etc rows in col P Conversely, this, array-entered: =AVERAGE(IF((MOD(ROW(P8:P235),2)=1)*(P8:P235<""), P8:P235)) will yield the average of the 9th, 11th, 13th, etc rows in col P -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Muralikrishnan" wrote: hi max thanks for your e-mail. i have tried it as below: but it appear error. =AVERAGE(IF(MOD(P8:P235,2),P8:P235)) i have in 8 th row % 9th row values, 10th row %, 11th row values like that until 235 row. can u pls reply. ---------------------------------- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to average every 48 rows of data for 20,000 rows? | Excel Discussion (Misc queries) | |||
Find monthly average but have average automatically configured | Excel Discussion (Misc queries) | |||
select date range then find average of values in another cell | Excel Worksheet Functions | |||
find an average from a range, utilizing all data 0 | Excel Worksheet Functions | |||
I need a formula to find rows within a date range in one column? | Excel Worksheet Functions |