J
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:B65536),15)=0)*(B15:B65536))
--
Vasant
- Hide quoted text -
- Show quoted text -
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 -
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 -
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 -
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.
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.