Putting a formula in place one time for the entire spreadsheet

[jacob]

Hello, I have a quick question, regarding this topic from a few months
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:B6553­6),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 -

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

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 -

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.

 

[Tom Ogilvy]

=SUM((MOD(ROW(L22:L352)-5,17)=0)*(IF(ISNUMBER(L22:L352),L22:L352,0)))

Should be what you want.

If I adjust the second part to return the row number and then evaluate the
part inside the sum I get:

{22;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;39;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;
56;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;73;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;
90;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;107;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;
124;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;141;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;
158;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;175;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;
192;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;209;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;
226;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;243;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;
260;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;277;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;
294;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;311;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;
328;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;345;0;0;0;0;0;0;0}

which shows that

22, 39, 56, 73, 90, 107, 124, 141, etc are the rows that will be summed.
--
Regards,
Tom Ogilvy


Hello, I have a quick question, regarding this topic from a few months
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:B6553­6),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 -

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

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 -

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.

 

[jacob]

Hi, the formula doesn't seem to work....
I have data in the L22, 39 cells, but L5 still shows "0".

Perhaps I'm doing something incorrectly? I can't figure it out...

Jacob