Automatically selection x+4th cell down from current row as next value in formula

**jonbasha1** · 08-05-2016, 08:31 AM

Hi all,
First off, thanks in advance for your help with my issue. I am usually able to solve my excel issues but I can’t seem to crack this one, it must be possible but I think I am just not using the right keywords to search or just not understanding properly.

You can look at the sample data I put in below (yes I know its not a real formula, I just wanted to get the point across

). Each sample matrix contains 12 individual data points (indicated by the positions on the matrix on the left). I want a ‘master’ matrix at the top of the page, which will continue to count any new matrices that I copy paste in below the existing ones. If it were a closed data set (no more matrices to be added in the future) what I would normally do is take the AVERAGE(G9;G13;G17) and be done with it. But instead I want it to be AVERAGE(G9;G13;G17;G21;G25…) to eteCapture.PNGrnity,so it will always include the x+4th cell from the current position (or if its easier,G9 then G9+4*1, then G9+4*2, etc (but not just G9+4 and then 8 but rather G9 then 4 rows down, then 8 rows down, then 12 rows down, etc). I would then expand this to all the other positions in the matrix.

I have a feeling I have over complicated this, but I can’t wrap my head around it. Can somebody help? I know I can just update the formula at the top each time I copy in new data, but it seems like there is a sleeker way to do this in Excel. Am I wrong?

Thank you!!

JB1

**Richard Buttrey** · 08-05-2016, 09:44 AM

Hi,

Please upload the workbook. These things are always easier of we can see the request in context.

**jonbasha1** · 08-05-2016, 09:59 AM

Hi there,

Thanks for your quick reply. I thought about that too but I really don't see how to upload files here... So I made it available here instead: https://www.dropbox.com/s/d69gocvy12...trix.xlsx?dl=0

**TudyBTH** · 08-05-2016, 10:21 AM

Hi,
Define Name Values with formula:

Formula:

Please Login or Register  to view this content.

and for average use this in A2, drag down and toright

Formula:

Please Login or Register  to view this content.

see attachamet

**Glenn Kennedy** · 08-05-2016, 10:46 AM

Try this Array formula in B9, copied across and down.

=AVERAGE(IF(MOD(ROW(G9:G17)-ROWS($1:1),4)=0,G9:G17))

Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

**jonbasha1** · 08-05-2016, 11:10 AM

Hi Glenn and TudyTBH

Thank you both for your replies.

Glenn--I tried your formula but it throws up an error, when I hit OK it highlights the ,4 you have right after the ROWS function. Not sure how to troubleshoot that.

TudyTBH--thank you for that. Your solution does work in your sample spreadsheet and I will use it in a pinch but I have no idea how to modify it for future dataset and as such if I can get the Array formula mentioned by Glenn to work as it seems much simpler to work with, at least to me.

Thanks to everyone for their input!

**jonbasha1** · 08-05-2016, 11:16 AM

Hi again Glenn,

Ok I got it to return a value (by replacing the commas at ,4 and 0, with semi-colons (this is a french version of excel, which i hate working with btw, so maybe that makes a difference), but when I try it, I get the average as 4.2 in B2, which is the average of column G and not just G9, G13, and G17.

Edit: ok I think changing that to semi colons messes up the Array portion of the function as I get the value even just by hitting enter. But when left as commas it still gives an error even with CTRL-SHIFT-ENTER (and I confirmed it is this same key combination for the french version of Excel)

Thanks,
JB1

**Glenn Kennedy** · 08-05-2016, 11:21 AM

It does. Mainland europe uses ; we use ,

If it's only the 3 you need... I'll be back...

**Glenn Kennedy** · 08-05-2016, 11:30 AM

How's this Array Formula. Add any numbers belo your table and it doesn't affect the calculated averages. Indeed I'm certain that my previous version didn't, either. here's the file, though.

**jonbasha1** · 08-05-2016, 11:32 AM

Thanks Glenn

As an update. I can confirm it recognizes as an array even after swapping the ; and translating the functions (Excel in a second language can be painful.........!). But yes pelase B9 would have to reflect average of only G9, G13, G17 (and G21, G25, G29, etc as I keep pasting in below). I understand how to extend the ranges of the formulae you gave me so that isn't a problem in the future.

Thanks!!

Edit: got ninja'd, sorry about that. To clarify, I want it to include the data pasted below as current averages... but only every 4th cell down from the starting cell (i.e. the same position on every new matrix pasted in). Not sure if this is possible with your formula as it is set to take average of entire column (within the range defined), but I could also jsut do that with =AVERAGE(G9:G19), no? Not trying to be snarky I just want to make sure I understand why you elaborated the formula as you did.

Double thanks!

**Glenn Kennedy** · 08-05-2016, 11:34 AM

The way you described your problem, it sounded (to me) that you wanted ONLY the first 3 values to be averaged... Back later...

**Glenn Kennedy** · 08-05-2016, 11:37 AM

Try this array formula, good for 1000 rows.

=AVERAGE(IF(MOD(ROW(G$9:G$1000)-ROWS($1:1),4)=0,IF($G$9:G$1000<>"",G$9:G$1000)))

**jonbasha1** · 08-05-2016, 11:44 AM

Bingo!!! Once "translated" it works perfectly!

Thanks so much for your help Glenn! And for sticking with it. I was having trouble being able to describe it and you still saw through that to save the day, haha. Thanks again!

**Glenn Kennedy** · 08-05-2016, 11:44 AM

You're welcome.

**TudyBTH** · 08-05-2016, 11:55 AM

For maximum portability, define another name: "average" in uper-left corner of data average range (A2 in my example, and put this formula

Formula:

Please Login or Register  to view this content.

now you can put tour table anywhere

Automatically selection x+4th cell down from current row as next value in formula

LinkBack

Thread Tools

Rate This Thread

Display

Automatically selection x+4th cell down from current row as next value in formula

Re: Automatically selection x+4th cell down from current row as next value in formula

Re: Automatically selection x+4th cell down from current row as next value in formula

Re: Automatically selection x+4th cell down from current row as next value in formula

Re: Automatically selection x+4th cell down from current row as next value in formula

Re: Automatically selection x+4th cell down from current row as next value in formula

Re: Automatically selection x+4th cell down from current row as next value in formula

Re: Automatically selection x+4th cell down from current row as next value in formula

Re: Automatically selection x+4th cell down from current row as next value in formula

Re: Automatically selection x+4th cell down from current row as next value in formula

Re: Automatically selection x+4th cell down from current row as next value in formula

Re: Automatically selection x+4th cell down from current row as next value in formula

Re: Automatically selection x+4th cell down from current row as next value in formula

Re: Automatically selection x+4th cell down from current row as next value in formula

Re: Automatically selection x+4th cell down from current row as next value in formula

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Cell automatically updates with current user

VBA - Autofill a formula where the current selection changes.

[SOLVED] Automatically adjust rectangle on cell value? Also overlapping issue with current formula

How to automatically update a formula based on current date

cursors move screen position instead of current cell selection - excel 2003

[SOLVED] Stop Users From Selecting Another Cell Until Current Selection Receives Data

[SOLVED] how do i automatically insert the current author's name in a cell.

Bookmarks

Bookmarks

Posting Permissions