Excel - Average every 3 rows AND insert answer every 3 rows
up vote
1
down vote
favorite
Overall problem: I want to be able to create a column (M) in an existing table that will average three values from column L and insert it in an adjacent cell.
Example:
Row I J K L M
1 [Sample number] [Replicate] [Hours Exposed] [YI] [AVERAGE YI]
2 1 1 0 12 13
3 1 2 0 13
4 1 3 0 14
5 2 1 0 10 11
6 2 2 0 11
7 2 3 0 12
Additional context if helpful: Chemistry data. The goal is to average the results from testing done in triplicate to get an overall value for a particular sample. Also want to be able to set up the database so that as additional data is added everything auto-calculates,and I can pull from it to create PivotCharts
This answer (excel averaging every 10 rows) was almost exactly what I need, the formula works perfectly, but it inserts the answer in sequential cells down (B1, B2, B3) so my "average" column doesn't line up with the relevant sample
This answer (How to find the average of every six cells in Excel) almost worked as well, with some changes to match my inputs, but I have a header row so everything starts at row 2 and for some reason that breaks the math. All the generated values are wrong because they're shifted by one cell and I haven't been able to figure out how to tell it "start at L2, not L1"
excel excel-formula
asked Nov 7 at 17:45
Deirdre Newton
82
add a comment |
up vote
1
down vote
favorite
Overall problem: I want to be able to create a column (M) in an existing table that will average three values from column L and insert it in an adjacent cell.
Example:
Row I J K L M
1 [Sample number] [Replicate] [Hours Exposed] [YI] [AVERAGE YI]
2 1 1 0 12 13
3 1 2 0 13
4 1 3 0 14
5 2 1 0 10 11
6 2 2 0 11
7 2 3 0 12
Additional context if helpful: Chemistry data. The goal is to average the results from testing done in triplicate to get an overall value for a particular sample. Also want to be able to set up the database so that as additional data is added everything auto-calculates,and I can pull from it to create PivotCharts
This answer (excel averaging every 10 rows) was almost exactly what I need, the formula works perfectly, but it inserts the answer in sequential cells down (B1, B2, B3) so my "average" column doesn't line up with the relevant sample
This answer (How to find the average of every six cells in Excel) almost worked as well, with some changes to match my inputs, but I have a header row so everything starts at row 2 and for some reason that breaks the math. All the generated values are wrong because they're shifted by one cell and I haven't been able to figure out how to tell it "start at L2, not L1"
excel excel-formula
asked Nov 7 at 17:45
Deirdre Newton
82
Try=IF(MOD(ROW()-1,3)=0,AVERAGE(INDEX(L:L,ROW()-2):INDEX(L:L,ROW())),"")- This may actually not work as it puts the value in the third cell in the range and not the first cell in the range of three
– Sorceri
Nov 7 at 20:24
I would have said =IF(MOD(ROW()+1,3)=0,AVERAGE(L2:L4),"") would be enough, the rest works by relative addressing as the formula is pulled down.
– Tom Sharpe
Nov 7 at 21:05
add a comment |
up vote
1
down vote
favorite
up vote
1
down vote
favorite
Overall problem: I want to be able to create a column (M) in an existing table that will average three values from column L and insert it in an adjacent cell.
Example:
Row I J K L M
1 [Sample number] [Replicate] [Hours Exposed] [YI] [AVERAGE YI]
2 1 1 0 12 13
3 1 2 0 13
4 1 3 0 14
5 2 1 0 10 11
6 2 2 0 11
7 2 3 0 12
Additional context if helpful: Chemistry data. The goal is to average the results from testing done in triplicate to get an overall value for a particular sample. Also want to be able to set up the database so that as additional data is added everything auto-calculates,and I can pull from it to create PivotCharts
This answer (excel averaging every 10 rows) was almost exactly what I need, the formula works perfectly, but it inserts the answer in sequential cells down (B1, B2, B3) so my "average" column doesn't line up with the relevant sample
This answer (How to find the average of every six cells in Excel) almost worked as well, with some changes to match my inputs, but I have a header row so everything starts at row 2 and for some reason that breaks the math. All the generated values are wrong because they're shifted by one cell and I haven't been able to figure out how to tell it "start at L2, not L1"
excel excel-formula
asked Nov 7 at 17:45
Deirdre Newton
82
Overall problem: I want to be able to create a column (M) in an existing table that will average three values from column L and insert it in an adjacent cell.
Example:
Row I J K L M
1 [Sample number] [Replicate] [Hours Exposed] [YI] [AVERAGE YI]
2 1 1 0 12 13
3 1 2 0 13
4 1 3 0 14
5 2 1 0 10 11
6 2 2 0 11
7 2 3 0 12
Additional context if helpful: Chemistry data. The goal is to average the results from testing done in triplicate to get an overall value for a particular sample. Also want to be able to set up the database so that as additional data is added everything auto-calculates,and I can pull from it to create PivotCharts
This answer (excel averaging every 10 rows) was almost exactly what I need, the formula works perfectly, but it inserts the answer in sequential cells down (B1, B2, B3) so my "average" column doesn't line up with the relevant sample
This answer (How to find the average of every six cells in Excel) almost worked as well, with some changes to match my inputs, but I have a header row so everything starts at row 2 and for some reason that breaks the math. All the generated values are wrong because they're shifted by one cell and I haven't been able to figure out how to tell it "start at L2, not L1"
excel excel-formula
excel excel-formula
asked Nov 7 at 17:45
Deirdre Newton
82
asked Nov 7 at 17:45
Deirdre Newton
82
asked Nov 7 at 17:45
Deirdre Newton
82
asked Nov 7 at 17:45
Deirdre Newton
82
asked Nov 7 at 17:45
Deirdre Newton
82
82
Try=IF(MOD(ROW()-1,3)=0,AVERAGE(INDEX(L:L,ROW()-2):INDEX(L:L,ROW())),"")- This may actually not work as it puts the value in the third cell in the range and not the first cell in the range of three
– Sorceri
Nov 7 at 20:24
I would have said =IF(MOD(ROW()+1,3)=0,AVERAGE(L2:L4),"") would be enough, the rest works by relative addressing as the formula is pulled down.
– Tom Sharpe
Nov 7 at 21:05
add a comment |
Try=IF(MOD(ROW()-1,3)=0,AVERAGE(INDEX(L:L,ROW()-2):INDEX(L:L,ROW())),"")- This may actually not work as it puts the value in the third cell in the range and not the first cell in the range of three
– Sorceri
Nov 7 at 20:24
I would have said =IF(MOD(ROW()+1,3)=0,AVERAGE(L2:L4),"") would be enough, the rest works by relative addressing as the formula is pulled down.
– Tom Sharpe
Nov 7 at 21:05
Try
=IF(MOD(ROW()-1,3)=0,AVERAGE(INDEX(L:L,ROW()-2):INDEX(L:L,ROW())),"") - This may actually not work as it puts the value in the third cell in the range and not the first cell in the range of three– Sorceri
Nov 7 at 20:24
Try
=IF(MOD(ROW()-1,3)=0,AVERAGE(INDEX(L:L,ROW()-2):INDEX(L:L,ROW())),"") - This may actually not work as it puts the value in the third cell in the range and not the first cell in the range of three– Sorceri
Nov 7 at 20:24
I would have said =IF(MOD(ROW()+1,3)=0,AVERAGE(L2:L4),"") would be enough, the rest works by relative addressing as the formula is pulled down.
– Tom Sharpe
Nov 7 at 21:05
I would have said =IF(MOD(ROW()+1,3)=0,AVERAGE(L2:L4),"") would be enough, the rest works by relative addressing as the formula is pulled down.
– Tom Sharpe
Nov 7 at 21:05
add a comment |
1 Answer
1
active
oldest
votes
up vote
1
down vote
accepted
You could try
=IF(MOD(ROW()+1,3)=0,AVERAGE(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN()-1,4)),0,0,3,1)),"")
answered Nov 7 at 17:51
QHarr
26.4k81839
1
Have upvoted his as not much different here.
– QHarr
Nov 7 at 17:53
Hi QHarr! Tried the modification, looks like this just pulls the value in L for Replicate 2 of each sample and inserts it into column M, rather than taking an average of all 3. It just trickily appears to be right using the simplified values in my example where the average = that middle number
– Deirdre Newton
Nov 7 at 19:02
I don't know what Replicate 2 is but it takes the average of every 3 rows starting from row 2.
– QHarr
Nov 7 at 19:03
So what I'm getting is that it's calculating the average of (5.55, 5.17, 5.42) to be 5.17 (so pulling the 2nd number, which I delineate by calling it Replicate 2) instead of the real average of 5.38
– Deirdre Newton
Nov 7 at 19:10
Try edited version.
– QHarr
Nov 7 at 19:43
|
show 1 more comment
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
accepted
You could try
=IF(MOD(ROW()+1,3)=0,AVERAGE(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN()-1,4)),0,0,3,1)),"")
answered Nov 7 at 17:51
QHarr
26.4k81839
1
Have upvoted his as not much different here.
– QHarr
Nov 7 at 17:53
Hi QHarr! Tried the modification, looks like this just pulls the value in L for Replicate 2 of each sample and inserts it into column M, rather than taking an average of all 3. It just trickily appears to be right using the simplified values in my example where the average = that middle number
– Deirdre Newton
Nov 7 at 19:02
I don't know what Replicate 2 is but it takes the average of every 3 rows starting from row 2.
– QHarr
Nov 7 at 19:03
So what I'm getting is that it's calculating the average of (5.55, 5.17, 5.42) to be 5.17 (so pulling the 2nd number, which I delineate by calling it Replicate 2) instead of the real average of 5.38
– Deirdre Newton
Nov 7 at 19:10
Try edited version.
– QHarr
Nov 7 at 19:43
|
show 1 more comment
up vote
1
down vote
accepted
You could try
=IF(MOD(ROW()+1,3)=0,AVERAGE(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN()-1,4)),0,0,3,1)),"")
answered Nov 7 at 17:51
QHarr
26.4k81839
1
Have upvoted his as not much different here.
– QHarr
Nov 7 at 17:53
Hi QHarr! Tried the modification, looks like this just pulls the value in L for Replicate 2 of each sample and inserts it into column M, rather than taking an average of all 3. It just trickily appears to be right using the simplified values in my example where the average = that middle number
– Deirdre Newton
Nov 7 at 19:02
I don't know what Replicate 2 is but it takes the average of every 3 rows starting from row 2.
– QHarr
Nov 7 at 19:03
So what I'm getting is that it's calculating the average of (5.55, 5.17, 5.42) to be 5.17 (so pulling the 2nd number, which I delineate by calling it Replicate 2) instead of the real average of 5.38
– Deirdre Newton
Nov 7 at 19:10
Try edited version.
– QHarr
Nov 7 at 19:43
|
show 1 more comment
up vote
1
down vote
accepted
up vote
1
down vote
accepted
You could try
=IF(MOD(ROW()+1,3)=0,AVERAGE(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN()-1,4)),0,0,3,1)),"")
answered Nov 7 at 17:51
QHarr
26.4k81839
You could try
=IF(MOD(ROW()+1,3)=0,AVERAGE(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN()-1,4)),0,0,3,1)),"")
answered Nov 7 at 17:51
QHarr
26.4k81839
edited Nov 7 at 19:42
answered Nov 7 at 17:51
QHarr
26.4k81839
answered Nov 7 at 17:51
QHarr
26.4k81839
answered Nov 7 at 17:51
QHarr
26.4k81839
26.4k81839
1
Have upvoted his as not much different here.
– QHarr
Nov 7 at 17:53
Hi QHarr! Tried the modification, looks like this just pulls the value in L for Replicate 2 of each sample and inserts it into column M, rather than taking an average of all 3. It just trickily appears to be right using the simplified values in my example where the average = that middle number
– Deirdre Newton
Nov 7 at 19:02
I don't know what Replicate 2 is but it takes the average of every 3 rows starting from row 2.
– QHarr
Nov 7 at 19:03
So what I'm getting is that it's calculating the average of (5.55, 5.17, 5.42) to be 5.17 (so pulling the 2nd number, which I delineate by calling it Replicate 2) instead of the real average of 5.38
– Deirdre Newton
Nov 7 at 19:10
Try edited version.
– QHarr
Nov 7 at 19:43
|
show 1 more comment
1
Have upvoted his as not much different here.
– QHarr
Nov 7 at 17:53
Hi QHarr! Tried the modification, looks like this just pulls the value in L for Replicate 2 of each sample and inserts it into column M, rather than taking an average of all 3. It just trickily appears to be right using the simplified values in my example where the average = that middle number
– Deirdre Newton
Nov 7 at 19:02
I don't know what Replicate 2 is but it takes the average of every 3 rows starting from row 2.
– QHarr
Nov 7 at 19:03
So what I'm getting is that it's calculating the average of (5.55, 5.17, 5.42) to be 5.17 (so pulling the 2nd number, which I delineate by calling it Replicate 2) instead of the real average of 5.38
– Deirdre Newton
Nov 7 at 19:10
Try edited version.
– QHarr
Nov 7 at 19:43
1
1
Have upvoted his as not much different here.
– QHarr
Nov 7 at 17:53
Have upvoted his as not much different here.
– QHarr
Nov 7 at 17:53
Hi QHarr! Tried the modification, looks like this just pulls the value in L for Replicate 2 of each sample and inserts it into column M, rather than taking an average of all 3. It just trickily appears to be right using the simplified values in my example where the average = that middle number
– Deirdre Newton
Nov 7 at 19:02
Hi QHarr! Tried the modification, looks like this just pulls the value in L for Replicate 2 of each sample and inserts it into column M, rather than taking an average of all 3. It just trickily appears to be right using the simplified values in my example where the average = that middle number
– Deirdre Newton
Nov 7 at 19:02
I don't know what Replicate 2 is but it takes the average of every 3 rows starting from row 2.
– QHarr
Nov 7 at 19:03
I don't know what Replicate 2 is but it takes the average of every 3 rows starting from row 2.
– QHarr
Nov 7 at 19:03
So what I'm getting is that it's calculating the average of (5.55, 5.17, 5.42) to be 5.17 (so pulling the 2nd number, which I delineate by calling it Replicate 2) instead of the real average of 5.38
– Deirdre Newton
Nov 7 at 19:10
So what I'm getting is that it's calculating the average of (5.55, 5.17, 5.42) to be 5.17 (so pulling the 2nd number, which I delineate by calling it Replicate 2) instead of the real average of 5.38
– Deirdre Newton
Nov 7 at 19:10
Try edited version.
– QHarr
Nov 7 at 19:43
Try edited version.
– QHarr
Nov 7 at 19:43
|
show 1 more comment
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53194940%2fexcel-average-every-3-rows-and-insert-answer-every-3-rows%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Try
=IF(MOD(ROW()-1,3)=0,AVERAGE(INDEX(L:L,ROW()-2):INDEX(L:L,ROW())),"")- This may actually not work as it puts the value in the third cell in the range and not the first cell in the range of three– Sorceri
Nov 7 at 20:24
I would have said =IF(MOD(ROW()+1,3)=0,AVERAGE(L2:L4),"") would be enough, the rest works by relative addressing as the formula is pulled down.
– Tom Sharpe
Nov 7 at 21:05