Copying Formulas Question

I need to copy and a paste a formula that will calculate the same thing for a large amount of data. My problem is I don't know how to copy a formula so that it will use the cells I need it to. My original formula is something like, (O3+O4*3)/4 and when I copy it down to the next line excel will make the formula (O4+O5*3)/4. What I need to know is how to make it skip to (O5+O6*3)/4, then the next line down (O7+O8*3)/4, then (O9+O10*3)/4 and so on.

if the intermediate cells are unused you can use a =if(row()=2*(int(row()/2)),your formula,"") this can be copied down through all rows

Or

use a macro to copy it down into every other row.

regards

I like this technique to build the formulas:

Put this in the top cell in your range:
="$$$(o"&ROW(A1)*2+1&"+o"&ROW(A1)*2+2&"*3)/4"

Notice that you don't actually have a formula that retrieves the value from the
Master Sheet. It's a formula that builds a formula.

Drag down the column as far as you need

Select that range
Edit|copy
Edit|paste special|values

With that range still selected
Edit|replace
what: $$$
with: = (equal sign)
replace all


"Tom." wrote:
>
> I need to copy and a paste a formula that will calculate the same thing
> for a large amount of data. My problem is I don't know how to copy a
> formula so that it will use the cells I need it to. My original formula
> is something like, (O3+O4*3)/4 and when I copy it down to the next line
> excel will make the formula (O4+O5*3)/4. What I need to know is how to
> make it skip to (O5+O6*3)/4, then the next line down (O7+O8*3)/4, then
> (O9+O10*3)/4 and so on.
>
> --
> Tom.
> ------------------------------------------------------------------------
> Tom.'s Profile: http://www.excelforum.com/member.php...o&userid=33766
> View this thread: http://www.excelforum.com/showthread...hreadid=535370

--

Dave Peterson

This was from a previous post:

Notice that you don't actually have a formula that retrieves the value from the
Master Sheet. It's a formula that builds a formula.

It should read:

Notice that you don't actually have a formula that does the calculation. It's a
formula that builds a formula.


Dave Peterson wrote:
>
> I like this technique to build the formulas:
>
> Put this in the top cell in your range:
> ="$$$(o"&ROW(A1)*2+1&"+o"&ROW(A1)*2+2&"*3)/4"
>
> Notice that you don't actually have a formula that retrieves the value from the
> Master Sheet. It's a formula that builds a formula.
>
> Drag down the column as far as you need
>
> Select that range
> Edit|copy
> Edit|paste special|values
>
> With that range still selected
> Edit|replace
> what: $$$
> with: = (equal sign)
> replace all
>
> "Tom." wrote:
> >
> > I need to copy and a paste a formula that will calculate the same thing
> > for a large amount of data. My problem is I don't know how to copy a
> > formula so that it will use the cells I need it to. My original formula
> > is something like, (O3+O4*3)/4 and when I copy it down to the next line
> > excel will make the formula (O4+O5*3)/4. What I need to know is how to
> > make it skip to (O5+O6*3)/4, then the next line down (O7+O8*3)/4, then
> > (O9+O10*3)/4 and so on.
> >
> > --
> > Tom.
> > ------------------------------------------------------------------------
> > Tom.'s Profile: http://www.excelforum.com/member.php...o&userid=33766
> > View this thread: http://www.excelforum.com/showthread...hreadid=535370
>
> --
>
> Dave Peterson

--

Dave Peterson

Or use Advanced Filter to remove every other row
of the original results.

ListA ListB ListB
0 7 7
4 11 15
8 15 23
12 19 31
16 23 39
20 27 47
24 31 55
28 35 63
32 39 71
36 43 79
40 47
44 51
48 55
52 59
56 63
60 67
64 71
68 75
72 79
76
80

Criteria
Test
=NOT(MOD(ROW(ListB),2))

Create the first ListB by copying down your original formula.
Advanced Filter creates the second ListB, filtered with Criteria.