Duplicate formulas downward?

[Rick C.]

[Fairly new to excel]: Can folks help me with this spreadsheet where my
rows are each day of the current month:

(1) In cell D1, I'd like to insert the formula "500 divided by the
number of rows I've entered". I.e. if it's a 31-day month I'll have 31
rows so the formula in D1 should be "=500/31"

(2) In all cells beneath D1, the formula should be "=D1*[rownumber]",
i.e. multiply the value in cell D1 times the row number (which in my
sheet is the day of the month).

Is there a way to automatically copy the formula down the entire D
column?

 

[Niek Otten]

Hi Rick,

Easiest is to introduce 2 extra cells. Let's say in column E. In E1, enter a
date in the month you're examining, like 1/30/2005 or whatever your local
date format is.
In E2, enter this formula:

=DAY(DATE(YEAR(E1),MONTH(E1)+1,0))

This will give you the number of days in that month; it will treat leap
years correctly as long as you don't enter the year 1900.

In D1:
=500/E2
In D2:
=IF(ROW()>$E$2,"",$D$1*ROW())

Copy down to D31, it will blank the non-existent days.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Rick C.]

=IF(ROW()>$E$2,"",$D$1*ROW())

Fantastic! THANK YOU, THANK YOU! Just what I needed. Uh-oh, now that
I see this is possible, how about this other column on the same sheet:

Column B refers to days of the month and has values entered only for
weekdays. So we'll have five weekday cells that should have numeric
data followed by two weekend cells with no data (I could make those '0'
if I need to). Cell B1 has a hard-coded value of 17.39. I want all
cells beneath it to have the formula: the sum of B1 and the first cell
above the current cell THAT HAS ANY VALUE IN IT. So B1-B19 should look
like this:

B1 17.39
B2 34.78 (B1+B1)
B3 52.17 (B2+B1)
B4 69.57 (B3+B1)
B5 86.96 (B4+B1)
B6
B7
B8 104.35 (B5+B1)
B9 121.74 (B8+B1)
B10 139.13 (B9+B1)
B11 156.52 (B10+B1)
B12 173.91 (B11+B1)
B13
B14
B15 191.30 (B12+B1)
B16 208.70 (B15+B1)
B17 226.09 (B16+B1)
B18 243.48 (B17+B1)
B19 260.87 (B18+B1)


Any help with doing this in a more programmatic fashion than I've done
it? Thanks very much.

 

[David McRitchie]

Hi Rick,

B2: =B1 + B$1

if you are going to be inserting rows, the following
would make it only necessary to put the formula
into the empty cell, rather than having to also fix old formulas.

B2: =OFFSET(B2,-1,0) + B$1

Another way that matches your example would be to use
the row number and not be dependent on previous calculations.
B2: =ROW() * B$1

And now I see that you basically already had that last one.
The difference is that column B doesn't need to be absolute.
If it is not absolute you can fill to the right as well.

Use of fill-handle
http://www.mvps.org/dmcritchie/excel/fillhand.htm
use of OFFSET Workshet Function
http://www.mvps.org/dmcritchie/excel/offset.htm

 

[Rick C.]

B2: =OFFSET(B2,-1,0) + B$1

David, thanks much for this. But because of the two weekend days (see
my example in previous message), after row 5 I DON'T want to multiply B1
by the row number. I need to add the value of B1 to the value of the
first cell above the current cell THAT ACTUALLY HAS DATA (weekdays
only). I'm not sure either of your examples allows for that; or am I
wrong? I'd like to do this WITHOUT hard-coding row numbers into the
formula as I've done.

 

[Max]

One try ..

With B1 containing: 17.39

Put in B2:

=IF(OR(MOD(ROWS($A$1:A1),7)+1=6,MOD(ROWS($A$1:A1),7)+1=7),"",IF(B1="",SUM(MA
X($B$1:B1),$B$1),SUM(B1,$B$1)))

Copy down

 

[Max]

One try ..

With B1 containing: 17.39

Put in B2:

=IF(OR(MOD(ROWS($A$1:A1),7)+1=6,MOD(ROWS($A$1:A1),7)+1=7),"",IF(B1="",SUM(MA
X($B$1:B1),$B$1),SUM(B1,$B$1)))

Copy down

 

[Max]

Maybe slightly shorter, try instead in B2 and copy down:

=IF(OR(MOD(ROWS($A$1:A1),7)+1={6;7}),"",IF(B1="",SUM(MAX($B$1:B1),$B$1),SUM(
B1,$B$1)))

 

[Rick C.]

Thanks much; inserting this formula in each cell in column B leaves me
with 34.78 (17.39 * 2) in every cell. Same with the formula in your
previous message :-(

By the way, I notice your use of 'A1' below -- did you mean to use that?

I really appreciate this help.

 

[Rick C.]

=IF(OR(MOD(ROWS($A$1:A1),7)+1={6;7}),"",IF(B1="",SUM(MAX($B$1:B1),$B$1),SUM(

(woops) -- I meant inserting the formula into each cell B2 and below.
B1 has 17.39.

 

[Max]

(woops) -- I meant inserting the formula

into each cell B2 and below.
B1 has 17.39.

Think you're implementing the "copy down" part for B2 wrongly <g>.

After you've put (pasted) the formula into B2, "Copy down" means to drag
down the bottom right corner of B2 until say B200 [or whatever your last
cell in column B may be]? The cursor will turn into a "black cross" when
you point at the bottom right corner of the cell (the fill handle).

Try it again. It should work ok. Post back.

 

[Max]

What you should get in col B
when you copy the formula in B2 downwards is:

17.39 < Number input in B1
34.78 < Formula placed in B2, then filled down to B400?
52.17
69.56
86.95
<Blank>
<Blank>
104.34
121.73
139.12
156.51
173.9
<Blank>
<Blank>
191.29
208.68
226.07
243.46
260.85
<Blank>
<Blank>
278.24
etc

--
If you still have difficulty, I could send you a sample book

Just drop me a line at either:

demechanik <at>yahoo<dot>com
or
xdemechanik <at>yahoo<dot>com

 

[Max]

By the way, I notice your use of 'A1' below -- did you mean to use that? .....
Maybe slightly shorter, try instead in B2 and copy down:

=IF(OR(MOD(ROWS($A$1:A1),7)+1={6;7}),"",IF(B1="",SUM(MAX($B$1:B1),$B$1),SUM(
B1,$B$1)))

Just a clarification on the use of .. ROWS($A$1:A1) in the formula

ROWS($A$1:A1) is used as an incrementer so that when we copy/fill the
formula down, this part will increment as: 1, 2, 3, 4, ... etc

You can see this happening by putting in any starting cell in an empty col,
say, put in Z10 : = ROWS($A$1:A1)

Then copy Z10 down to say Z15

In Z10:Z15 will be returned the numbers: 1,2,3,4,5,6

We could have also used instead: ROWS($B$1:B1) or ROWS($C$1:C1)
which would return the same as ROWS($A$1:A1) when filled down

(using "A1" is just a kind of "usual" practice)

 

[Rick C.]

YEAH! Yup, that did it. Thanks VERY much. Much appreciated.

(woops) -- I meant inserting the formula
into each cell B2 and below.
B1 has 17.39.

Think you're implementing the "copy down" part for B2 wrongly <g>.

After you've put (pasted) the formula into B2, "Copy down" means to drag
down the bottom right corner of B2 until say B200 [or whatever your last
cell in column B may be]? The cursor will turn into a "black cross" when
you point at the bottom right corner of the cell (the fill handle).

Try it again. It should work ok. Post back.

 

[Max]

YEAH! Yup, that did it. Thanks VERY much. Much appreciated.

You're welcome !
Thanks for posting back ..