Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
My first post - Please Help (Formula for adding Values)
Hello !! HELP!! I have a report I need to finish by the end of the week and I am dealing with hundreds of rows. I need to be able to add up the total of every 4th row only. I cannot for the life of me figure out what forumla I need to use. Any help would be greatly appreciated!! Thanks, LeapyLeigh :) :) :) -- leapyleigh ------------------------------------------------------------------------ leapyleigh's Profile: http://www.excelforum.com/member.php...o&userid=36275 View this thread: http://www.excelforum.com/showthread...hreadid=560565 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
My first post - Please Help (Formula for adding Values)
"leapyleigh" wrote
in message ... Hello !! HELP!! I have a report I need to finish by the end of the week and I am dealing with hundreds of rows. I need to be able to add up the total of every 4th row only. I cannot for the life of me figure out what forumla I need to use. Any help would be greatly appreciated!! One way is to use a formula like =IF(MOD(ROW(),4)=1,A1,"") and total that column. There will doubtless be clever things with SUMIF and SUMPRODUCT that other contributors will be able to suggest. -- David Biddulph |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
My first post - Please Help (Formula for adding Values)
leapyleigh wrote:
I need to be able to add up the total of every 4th row only. Do we have the same understanding of what "every 4th row" means? To me, it means A1, A5, A9, etc for example. The following array formula will do that (every 4th row starting A1 and ending with the 4th row at or before A17): =SUM( IF(MOD(ROW(A1:A17)-ROW(A1),4)=0, A1:A17) ) Type ctrl-shift-Enter, not Enter, after typing or editing the formula. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
My first post - Please Help (Formula for adding Values)
PS.....
I wrote: leapyleigh wrote: I need to be able to add up the total of every 4th row only. Do we have the same understanding of what "every 4th row" means? To me, it means A1, A5, A9, etc for example. The following array formula will do that (every 4th row starting A1 and ending with the 4th row at or before A17): =SUM( IF(MOD(ROW(A1:A17)-ROW(A1),4)=0, A1:A17) ) Type ctrl-shift-Enter, not Enter, after typing or editing the formula. I should note that that array formula works regardless of where the range begins. For example, if you wanted every 4th row starting in A2, simply replace all occurrences of A1 with A2. Also, when I wrote "ending with the 4th row at or before A17", I meant: counting from the first row of the range, not from the end of the range. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
My first post - Please Help (Formula for adding Values)
=SUMPRODUCT(--(MOD(ROW(A1:A1000,4)=1)),A1:A1000)
this adds row 1,5,9, etc. if you want 2,6,10 change =1 to =2, 3,7,11 change =1 to =3, and finally, 4,8,12 change =1 to =0 -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "leapyleigh" wrote in message ... Hello !! HELP!! I have a report I need to finish by the end of the week and I am dealing with hundreds of rows. I need to be able to add up the total of every 4th row only. I cannot for the life of me figure out what forumla I need to use. Any help would be greatly appreciated!! Thanks, LeapyLeigh :) :) :) -- leapyleigh ------------------------------------------------------------------------ leapyleigh's Profile: http://www.excelforum.com/member.php...o&userid=36275 View this thread: http://www.excelforum.com/showthread...hreadid=560565 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
My first post - Please Help (Formula for adding Values)
Bob Phillips wrote:
=SUMPRODUCT(--(MOD(ROW(A1:A1000,4)=1)),A1:A1000) this adds row 1,5,9, etc. if you want 2,6,10 change =1 to =2, 3,7,11 change =1 to =3, and finally, 4,8,12 change =1 to =0 Besides the obvious typo (misplaced right parenthesis for MOD), the problem with solutions like this, which rely simply on the MOD of the row number, is that if the range is moved (e.g. insert a row above it), the formula no longer works unless the user remembers to change it. In this case, I prefer the more obvious array formulation, even though I usually prefer to avoid array formulas. But if you are enamored to arcane SUMPRODUCT formulations (ever wonder why so many people ask what "--" means?), I would opt for combining the two ideas as follows: =sumproduct( --(mod(row(A1:A1000)-row(A1),4)=0), A1:A1000 ) Of course, replace A1:A1000 with the correct range. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
My first post - Please Help (Formula for adding Values)
Arcane, don't make me laugh.
Ever wondered why people ask why an array formula doesn't work? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message ups.com... Bob Phillips wrote: =SUMPRODUCT(--(MOD(ROW(A1:A1000,4)=1)),A1:A1000) this adds row 1,5,9, etc. if you want 2,6,10 change =1 to =2, 3,7,11 change =1 to =3, and finally, 4,8,12 change =1 to =0 Besides the obvious typo (misplaced right parenthesis for MOD), the problem with solutions like this, which rely simply on the MOD of the row number, is that if the range is moved (e.g. insert a row above it), the formula no longer works unless the user remembers to change it. In this case, I prefer the more obvious array formulation, even though I usually prefer to avoid array formulas. But if you are enamored to arcane SUMPRODUCT formulations (ever wonder why so many people ask what "--" means?), I would opt for combining the two ideas as follows: =sumproduct( --(mod(row(A1:A1000)-row(A1),4)=0), A1:A1000 ) Of course, replace A1:A1000 with the correct range. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
My first post - Please Help (Formula for adding Values)
Bob Phillips wrote:
Arcane, don't make me laugh. Ever wondered why people ask why an array formula doesn't work? Touche'. I expected that response, exactly as you said it ;-). I don't think either one of us can prove which is more confusing and which invokes more questions. It's a moot point. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
My first post - Please Help (Formula for adding Values)
Well I am glad to see a smile after the tone of the previous post.
Personally, I don't think it is a moot point, or that one is more confusing than the other. Neither is arcane, they both have vagaries, idiosyncrasies, call it what you may, that you need to understand if you want to use them effectively. If you (you being one, the OP) just wants a solution (which too many do unfortunately), then the SUMPRODUCT solution is better as it will work out of the box, not need to explain anything such as CSE. I am not saying that is an argument in favour of SP, just a factor. Myself, I think array formulae are great, I think that SP formulae are great. Sometimes better to use one, sometimes the other. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message oups.com... Bob Phillips wrote: Arcane, don't make me laugh. Ever wondered why people ask why an array formula doesn't work? Touche'. I expected that response, exactly as you said it ;-). I don't think either one of us can prove which is more confusing and which invokes more questions. It's a moot point. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
My first post - Please Help (Formula for adding Values)
Another small thing to add is that once you understand how -- works, it can
be used elsewhere, such as testing a date so --"2006-07-12", which makes more readable formulae IMO, which is very important. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message oups.com... Bob Phillips wrote: Arcane, don't make me laugh. Ever wondered why people ask why an array formula doesn't work? Touche'. I expected that response, exactly as you said it ;-). I don't think either one of us can prove which is more confusing and which invokes more questions. It's a moot point. |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
My first post - Please Help (Formula for adding Values)
Welcome to the club<g
I would bet very few have any formal Excel training. Trial and error and brain-picking is the norm. Gord Dibben MS Excel MVP On Wed, 12 Jul 2006 18:57:55 -0400, leapyleigh wrote: I have had no "official" training on excel, everything I know has been figured out on the job, and from picking other peoples brains!! |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
My first post - Please Help (Formula for adding Values)
Waht's training?
"Gord Dibben" <gorddibbATshawDOTca wrote in message ... Welcome to the club<g I would bet very few have any formal Excel training. Trial and error and brain-picking is the norm. Gord Dibben MS Excel MVP On Wed, 12 Jul 2006 18:57:55 -0400, leapyleigh wrote: I have had no "official" training on excel, everything I know has been figured out on the job, and from picking other peoples brains!! |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
My first post - Please Help (Formula for adding Values)
indide, wots speeling?
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Bob Phillips" wrote in message ... Waht's training? "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Welcome to the club<g I would bet very few have any formal Excel training. Trial and error and brain-picking is the norm. Gord Dibben MS Excel MVP On Wed, 12 Jul 2006 18:57:55 -0400, leapyleigh wrote: I have had no "official" training on excel, everything I know has been figured out on the job, and from picking other peoples brains!! |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
My first post - Please Help (Formula for adding Values)
Geez Bob.
You shood yews your speel chicken befour pasting to theez groops On Thu, 13 Jul 2006 11:56:23 +0100, "Bob Phillips" wrote: indide, wots speeling? Gord Dibben MS Excel MVP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
automate replace values in formula | Excel Discussion (Misc queries) | |||
Lookup Data in two seperate Spreadsheets | Excel Worksheet Functions | |||
SUM formula - using variable values | Excel Worksheet Functions | |||
Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
How do I copy cell values (derived from formula), not references? | Excel Worksheet Functions |