Drag to copy every other row in Excel

[Carthesis]

Looking for some inspiration.

In my job (engineering) I very frequently come across a problem where data is output in columns of data, where I might have two slightly different values at any given point, ie:

        A        B
1     Coord    Value
2      0.0     25.325
3      0.4     25.221
4     10.0     15.417
5     10.5     15.339
6     20.0     10.778
7     20.3     10.912

Normally this comes from modelling where results have been extracted and it is giving results either side of a nodal point.

What I'd like to be able to do is write a formula (or a custom function) that averages the values and presents them in a similar format, so the above table would become:

        A        B        C	       D
1     Coord    Value   Av. Coord.  Av. Value
2      0.0     25.325     0.2	     25.273        [(0.0 + 0.4) / 2]   and   [(25.325 + 25.221) / 2]
3      0.4     25.221    10.25	     15.378        [(10.0 + 10.5) / 2]   and   [(15.417 + 15.339) / 2]
4     10.0     15.417    20.15	     10.845        [(20.0 + 20.3) / 2]   and   [(10.778 + 10.912) / 2]
5     10.5     15.339     ...         ...
6     20.0     10.778
7     20.3     10.912
8      ...       ...      ...          ...
...
...
...

etc.

I've done something like this before, but it was a very cumbersome formula using nested OFFSET() and ROW() functions, combined with a few other factors that just made it not easy to use.

Anyone got any ideas?

 

[SkipVought]

hi,

I'm trying to understand how your title relates to your question becaues nothing is copied.

Check out the MOD function, using ROW() and 2, returning 1 or 0 for any row. This will help determine the row on which the formula shoudl return the value.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lionelhill]

Another approach is to use an if. There are various ways to do it, but the simplest is where all the data falls in pairs. You can then use something like:
if(abs(A2-A1)<2, average(A1, A2), "")
This can be copied down the whole of column B (or whereever) but will only put entries on lines where there are two values "worth averaging".

It is possible to adapt this technique to find runs of varying lengths, always assuming you have a safe window in which the values to be averaged will fall.

 

[SkipVought]

BTW, a worthy objective in creating a formula is to be able to use it in EVERY ROW, and depending on the requirements EVERY ROW and EVERY COLUMN.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Carthesis]

Something is copied - you're copying a formula down.

And I know that in a lot of cases, you do want a formula in every row. In this case, I don't because I'm trying to get data processed in a specific manner in a specific format.

Gaps in the rows and everything else aren't really a solution I'm afraid, as the output i'm trying to get won't actually accept 'missing' rows.

Looks like I'll have to stick to sorting it out manually every time. Oh well.

 

[SkipVought]

Put A in C1 and B in D1 and then paste this formula in C2
[tt]
=(INDIRECT(C$1&ROW()*2-2)+INDIRECT(C$1&ROW()*2-1))/2
[/tt]
See how that works for you.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

Looks like I'll have to stick to sorting it out manually every time. Oh well.

If you don't get on with Skip's last solution then how about:

Using the solution in Skip's first post to identify every other row using the Mod and Row functions.

Do that in a 'helper' column.

Use your original formulae. Filter the Helper column to display just the rows you want.
Copy will copy just the visible cells and your desired results can be pasted to a new sheet.




Gavin

 

[lionelhill]

OK, here's another approach.
Put your data in column A, starting in cell A2.
Put the value 1 in B2
Put the following formula in B3, and copy down as far as you want:
=IF(ABS(A3-A2) > 0.5, B2+1, B2)
This will number each group of similar measurements successively 1, 2, 3. The value "0.5" is the tolerance between successive measurements before a new group is deemed to start, and you can use any value suited to your data.
Put the following formula in C2:
=COUNTIF(B:B, B2)
This now counts the number of members of each group.
Put the following in D2:
=IF(B2<>B1, A2/C2, A2/C2+D1)
This creates a running sum that at the end of each group is equal to the average measurement in the group. Now, we want to be able to look up this measurement by group, but we need the final value only. There are various ways to do it, but I'm in a hurry, so here's a quick-to-think but not optimal approach.
Put in E3:
=IF(B2<>B3, D2, E3)
This "back propagates" the correct mean up through the whole group, so the first line corresponding to that group now contains the correct average.

Now, finally, put in some column somewhere the values 1, 2, 3, 4, 5... etc. downwards (unless you want to use "ROW" as per Skip's instructions). I put my numbers in G2 onwards. Then put in the next cell (for me, H2):
=VLOOKUP(G2,B:F, 4, FALSE)

Voila, no gaps, and variable group-size is perfectly acceptable.