J

#### jacob

back...

I have this formula in L5:

=SUM((MOD(ROW(L20:L350)-5,15)=0)*(IF(ISNUMBER(L20:L350),L20:L350,0)))

My problem is, I'm inserting 2 rows in each range of 14, all the way to

the bottom of my worksheet. The effect is I'm changing the target cells

that I need added FROM L20 to L22, and I need every 17th cell after L22

summed. (So, the next cell I need to add is L39, and so on)

How can I modify the above formula to make this change? I tried

changing the "15" to 17, but that doesn't work. Any suggestions? I

don't know a lot about MOD formulas.

Thank-you

Putting a formula in place one time for the entire spreadsheet

« Start of topic « Older Messages 1 - 8 of 8 Newer » End of

topic »

1. jacob May 3, 6:45 pm show options

Newsgroups: microsoft.public.excel.programming

From: "jacob" <[email protected]> - Find messages by this author

Date: 3 May 2005 15:45:26 -0700

Local: Tues, May 3 2005 6:45 pm

Subject: Putting a formula in place one time for the entire spreadsheet

Reply to Author | Forward | Print | Individual Message | Show original

| Remove | Report Abuse

I would like to add the number displayed in every 15th cell in the B

column (starting with B20) and display those results in B5. Instead of

using

=(B20+B35+B50...)

is there a quicker formula I can use?

ALSO:

Is there a workday formula I can use to put the first workday of any

given month in a target cell, and then every 15th cell in the same row,

put the following day?

So: A1= 5/2/05

A16= 5/3/05

and so on...

Thanks guys!!!!!!!!!!!!!

2. Vasant Nanavati May 3, 7:09 pm show options

Newsgroups: microsoft.public.excel.programming

From: "Vasant Nanavati" <vasantn *AT* aol *DOT* com> - Find messages by

this author

Date: Tue, 3 May 2005 19:09:44 -0400

Local: Tues, May 3 2005 7:09 pm

Subject: Re: Putting a formula in place one time for the entire

spreadsheet

Reply to Author | Forward | Print | Individual Message | Show original

| Report Abuse

Every 15th cell all the way to the bottom of the worksheet???

=SUMPRODUCT((MOD(ROW(B15:B65536),15)=0)*(B15:B65536))

--

Vasant

- Hide quoted text -

- Show quoted text -

I would like to add the number displayed in every 15th cell in the B

column (starting with B20) and display those results in B5. Instead of

using

=(B20+B35+B50...)

is there a quicker formula I can use?

Is there a workday formula I can use to put the first workday of any

given month in a target cell, and then every 15th cell in the same row,

put the following day?

So: A1= 5/2/05

A16= 5/3/05

and so on...

Thanks guys!!!!!!!!!!!!!

3. jacob May 4, 11:08 am show options

Newsgroups: microsoft.public.excel.programming

From: "jacob" <[email protected]> - Find messages by this author

Date: 4 May 2005 08:08:11 -0700

Local: Wed, May 4 2005 11:08 am

Subject: Re: Putting a formula in place one time for the entire

spreadsheet

Reply to Author | Forward | Print | Individual Message | Show original

| Remove | Report Abuse

Hi,

I used your formula, but it gives a #VALUE in B5 instead of the total.

Any ideas?

Technically, I want the formula to add every 15th cell, starting with

B15 and ending with cell B350. I modified the above formula to end in

350, but I still get a #VALUE displayed. Any ideas?

Thanks again

4. Vasant Nanavati May 4, 3:59 pm show options

Newsgroups: microsoft.public.excel.programming

From: "Vasant Nanavati" <vasantn *AT* aol *DOT* com> - Find messages by

this author

Date: Wed, 4 May 2005 15:59:22 -0400

Local: Wed, May 4 2005 3:59 pm

Subject: Re: Putting a formula in place one time for the entire

spreadsheet

Reply to Author | Forward | Print | Individual Message | Show original

| Report Abuse

It works for me. Perhaps you have text in some of the cells?

--

Vasant

- Hide quoted text -

- Show quoted text -

I used your formula, but it gives a #VALUE in B5 instead of the total.

Any ideas?

Technically, I want the formula to add every 15th cell, starting with

B15 and ending with cell B350. I modified the above formula to end in

350, but I still get a #VALUE displayed. Any ideas?

Thanks again

5. jacob May 4, 6:52 pm show options

Newsgroups: microsoft.public.excel.programming

From: "jacob" <[email protected]> - Find messages by this author

Date: 4 May 2005 15:52:33 -0700

Local: Wed, May 4 2005 6:52 pm

Subject: Re: Putting a formula in place one time for the entire

spreadsheet

Reply to Author | Forward | Print | Individual Message | Show original

| Remove | Report Abuse

No text. (there are some blanks, some of the B column cells have not

been filled out with a number yet. Those cells contain a formula, but

no value)Would you mind me sending you the document? i have to be doing

something wrong.

Jacob

(e-mail address removed)

- Hide quoted text -

- Show quoted text -

Vasant said:It works for me. Perhaps you have text in some of the cells?

6. Vasant Nanavati May 4, 9:21 pm show options

Newsgroups: microsoft.public.excel.programming

From: "Vasant Nanavati" <vasantn *AT* aol *DOT* com> - Find messages by

this author

Date: Wed, 4 May 2005 21:21:12 -0400

Local: Wed, May 4 2005 9:21 pm

Subject: Re: Putting a formula in place one time for the entire

spreadsheet

Reply to Author | Forward | Print | Individual Message | Show original

| Report Abuse

Yes, go ahead and email it to me.

--

Vasant

- Hide quoted text -

- Show quoted text -

No text. (there are some blanks, some of the B column cells have not

been filled out with a number yet. Those cells contain a formula, but

no value)Would you mind me sending you the document? i have to be doing

something wrong.

7. jacob May 5, 9:28 pm show options

Newsgroups: microsoft.public.excel.programming

From: "jacob" <[email protected]> - Find messages by this author

Date: 5 May 2005 18:28:06 -0700

Local: Thurs, May 5 2005 9:28 pm

Subject: Re: Putting a formula in place one time for the entire

spreadsheet

Reply to Author | Forward | Print | Individual Message | Show original

| Remove | Report Abuse

I emailed it to you. Please let me know if it did NOT come thru.

thanks, in advance.

jacob

8. Vasant Nanavati May 6, 10:39 am show options

Newsgroups: microsoft.public.excel.programming

From: "Vasant Nanavati" <vasantn *AT* aol *DOT* com> - Find messages by

this author

Date: Fri, 6 May 2005 10:39:44 -0400

Local: Fri, May 6 2005 10:39 am

Subject: Re: Putting a formula in place one time for the entire

spreadsheet

Reply to Author | Forward | Print | Individual Message | Show original

| Report Abuse

Sorry, I'm on the road and am having trouble downloading files. I will

definitely look at it over the weekend.