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

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


--
tony h
------------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
View this thread: http://www.excelforum.com/showthread...hreadid=535370

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.