Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
in Excel: how can I make excel count 5 as 15 (5+4+3+2+1)
I need excel to do something to whatever digit is filled out in a cell.
Like if I would fill out 3 in A1, A2 would spit out: 6 because (3+2+1=6) This is particularly used in the sum of the years method. However I don't need that function, I just need A2 to spit out what I mentioned already. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
in Excel: how can I make excel count 5 as 15 (5+4+3+2+1)
What is the something that you want done to the digit entered?
-- Brevity is the soul of wit. "Carl" wrote: I need excel to do something to whatever digit is filled out in a cell. Like if I would fill out 3 in A1, A2 would spit out: 6 because (3+2+1=6) This is particularly used in the sum of the years method. However I don't need that function, I just need A2 to spit out what I mentioned already. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
in Excel: how can I make excel count 5 as 15 (5+4+3+2+1)
This should work for you:
=A1*(A1+1)/2 -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Carl" wrote in message ... I need excel to do something to whatever digit is filled out in a cell. Like if I would fill out 3 in A1, A2 would spit out: 6 because (3+2+1=6) This is particularly used in the sum of the years method. However I don't need that function, I just need A2 to spit out what I mentioned already. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
in Excel: how can I make excel count 5 as 15 (5+4+3+2+1)
Maybe something like this?
With a number in Cell A1 B1: =SUMPRODUCT(ROW($A$1:INDEX($A:$A,A1))) Does that help? *********** Regards, Ron XL2002, WinXP "Carl" wrote: I need excel to do something to whatever digit is filled out in a cell. Like if I would fill out 3 in A1, A2 would spit out: 6 because (3+2+1=6) This is particularly used in the sum of the years method. However I don't need that function, I just need A2 to spit out what I mentioned already. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
in Excel: how can I make excel count 5 as 15 (5+4+3+2+1)
Carl,
In A2, use the formula =SUMPRODUCT(ROW(INDIRECT("1:" & A1))*1) HTH, Bernie MS Excel MVP "Carl" wrote in message ... I need excel to do something to whatever digit is filled out in a cell. Like if I would fill out 3 in A1, A2 would spit out: 6 because (3+2+1=6) This is particularly used in the sum of the years method. However I don't need that function, I just need A2 to spit out what I mentioned already. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
in Excel: how can I make excel count 5 as 15 (5+4+3+2+1)
Yup....as soon as I posted, my "math brain" belatedly engaged and reminded me
of that formula. *********** Regards, Ron XL2002, WinXP "RagDyer" wrote: This should work for you: =A1*(A1+1)/2 -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Carl" wrote in message ... I need excel to do something to whatever digit is filled out in a cell. Like if I would fill out 3 in A1, A2 would spit out: 6 because (3+2+1=6) This is particularly used in the sum of the years method. However I don't need that function, I just need A2 to spit out what I mentioned already. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
in Excel: how can I make excel count 5 as 15 (5+4+3+2+1)
I think this would work mo' bettah.
=(A1^2+A1)/2 On Fri, 20 Oct 2006 12:20:31 -0700, "RagDyer" wrote: This should work for you: =A1*(A1+1)/2 Gord Dibben MS Excel MVP |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
in Excel: how can I make excel count 5 as 15 (5+4+3+2+1)
oops!!
Not better, just the same. Sorry RD On Fri, 20 Oct 2006 16:16:33 -0700, Gord Dibben <gorddibbATshawDOTca wrote: I think this would work mo' bettah. =(A1^2+A1)/2 On Fri, 20 Oct 2006 12:20:31 -0700, "RagDyer" wrote: This should work for you: =A1*(A1+1)/2 Gord Dibben MS Excel MVP Gord Dibben MS Excel MVP |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
in Excel: how can I make excel count 5 as 15 (5+4+3+2+1)
Carl....Yah 3+2+1 = 6, but you say sum years----what does 3 pertains in
relation to 2 and 1. if you input 3 - what does 3 stands for ? 3 years ? Then what is 2 and 1 for ? I cant see a sense to solve this problem of yours ? "Bernie Deitrick" wrote: Carl, In A2, use the formula =SUMPRODUCT(ROW(INDIRECT("1:" & A1))*1) HTH, Bernie MS Excel MVP "Carl" wrote in message ... I need excel to do something to whatever digit is filled out in a cell. Like if I would fill out 3 in A1, A2 would spit out: 6 because (3+2+1=6) This is particularly used in the sum of the years method. However I don't need that function, I just need A2 to spit out what I mentioned already. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
in Excel: how can I make excel count 5 as 15 (5+4+3+2+1)
One brick goes on top of two bricks, across the join.
Two bricks go on top of three bricks across the joins etc. The desired "pyramid" is 5 bricks high, how many bricks? That is just one example. Similar scenarios pop up all over the place in the real world. "romelsb" wrote in message ... Carl....Yah 3+2+1 = 6, but you say sum years----what does 3 pertains in relation to 2 and 1. if you input 3 - what does 3 stands for ? 3 years ? Then what is 2 and 1 for ? I cant see a sense to solve this problem of yours ? "Bernie Deitrick" wrote: Carl, In A2, use the formula =SUMPRODUCT(ROW(INDIRECT("1:" & A1))*1) HTH, Bernie MS Excel MVP "Carl" wrote in message ... I need excel to do something to whatever digit is filled out in a cell. Like if I would fill out 3 in A1, A2 would spit out: 6 because (3+2+1=6) This is particularly used in the sum of the years method. However I don't need that function, I just need A2 to spit out what I mentioned already. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
in Excel: how can I make excel count 5 as 15 (5+4+3+2+1)
For the bricks, You are right David...but as u said,,,in the real
world....years are not treated for such logical application.... "David F Cox" wrote: One brick goes on top of two bricks, across the join. Two bricks go on top of three bricks across the joins etc. The desired "pyramid" is 5 bricks high, how many bricks? That is just one example. Similar scenarios pop up all over the place in the real world. "romelsb" wrote in message ... Carl....Yah 3+2+1 = 6, but you say sum years----what does 3 pertains in relation to 2 and 1. if you input 3 - what does 3 stands for ? 3 years ? Then what is 2 and 1 for ? I cant see a sense to solve this problem of yours ? "Bernie Deitrick" wrote: Carl, In A2, use the formula =SUMPRODUCT(ROW(INDIRECT("1:" & A1))*1) HTH, Bernie MS Excel MVP "Carl" wrote in message ... I need excel to do something to whatever digit is filled out in a cell. Like if I would fill out 3 in A1, A2 would spit out: 6 because (3+2+1=6) This is particularly used in the sum of the years method. However I don't need that function, I just need A2 to spit out what I mentioned already. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
in Excel: how can I make excel count 5 as 15 (5+4+3+2+1)
Well Gord, with no intention of implying that I'm well versed in
mathematics, I just happen to take a liking to this "Arithmetic Series". A year or so ago, Dana put me on to a link (which I can't find at the moment) that described how Gauss formulated this "truth". In words it's: <<<"The count of sequential numbers times the average of the first and last number." =A1*(A1+1)/2 So, the formula I posted is supposed to be "intuitive" for *ALL* possibilities. Here, the OP's request was for a sum of a series starting (or ending) with 1, therefore, the last (or first) number in this series is the *actual* amount (count) of numbers in the sequential series, so your A1^2 works. BUT ... what if the we're looking for, say 5 to 15? A1 = 15 B1 = 5 In the original formula, A1 now has to become (A1-B1+1) And the balance becomes (A1+B1)/2 So we now have: =(A1-B1+1)*(A1+B1)/2 Taking it a step further, there's *no* stipulation that the sequential series must have an increment or decrement constant of *1*. Let's take the same 5 to 15, but here we want an increment constant of 5, the SUM of the sequential series of 5, 10, &15. A1 = 15 B1 = 5 C1 = 5 =((A1-B1)/C1+1)*(A1+B1)/2 So, that's why: <<<"The count of sequential numbers times the average of the first and last number." =A1*(A1+1)/2 Is probably easier to relate to. -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "Gord Dibben" <gorddibbATshawDOTca wrote in message ... oops!! Not better, just the same. Sorry RD On Fri, 20 Oct 2006 16:16:33 -0700, Gord Dibben <gorddibbATshawDOTca wrote: I think this would work mo' bettah. =(A1^2+A1)/2 On Fri, 20 Oct 2006 12:20:31 -0700, "RagDyer" wrote: This should work for you: =A1*(A1+1)/2 Gord Dibben MS Excel MVP Gord Dibben MS Excel MVP |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
in Excel: how can I make excel count 5 as 15 (5+4+3+2+1)
RD
I will ponder this for a while then quietly drift off to whatever planet I currently reside on. Depends upon which set of meds I'm on<g But I am improving.....honestly! Thanks for the work you put into this explanation. Saved for future regurgitation. Gord On Sat, 21 Oct 2006 12:56:31 -0700, "RagDyeR" wrote: Well Gord, with no intention of implying that I'm well versed in mathematics, I just happen to take a liking to this "Arithmetic Series". A year or so ago, Dana put me on to a link (which I can't find at the moment) that described how Gauss formulated this "truth". In words it's: <<<"The count of sequential numbers times the average of the first and last number." =A1*(A1+1)/2 So, the formula I posted is supposed to be "intuitive" for *ALL* possibilities. Here, the OP's request was for a sum of a series starting (or ending) with 1, therefore, the last (or first) number in this series is the *actual* amount (count) of numbers in the sequential series, so your A1^2 works. BUT ... what if the we're looking for, say 5 to 15? A1 = 15 B1 = 5 In the original formula, A1 now has to become (A1-B1+1) And the balance becomes (A1+B1)/2 So we now have: =(A1-B1+1)*(A1+B1)/2 Taking it a step further, there's *no* stipulation that the sequential series must have an increment or decrement constant of *1*. Let's take the same 5 to 15, but here we want an increment constant of 5, the SUM of the sequential series of 5, 10, &15. A1 = 15 B1 = 5 C1 = 5 =((A1-B1)/C1+1)*(A1+B1)/2 So, that's why: <<<"The count of sequential numbers times the average of the first and last number." =A1*(A1+1)/2 Is probably easier to relate to. Gord Dibben MS Excel MVP |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
in Excel: how can I make excel count 5 as 15 (5+4+3+2+1)
Found that link!
Here it is: http://mathworld.wolfram.com/ArithmeticSeries.html -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "Gord Dibben" <gorddibbATshawDOTca wrote in message ... RD I will ponder this for a while then quietly drift off to whatever planet I currently reside on. Depends upon which set of meds I'm on<g But I am improving.....honestly! Thanks for the work you put into this explanation. Saved for future regurgitation. Gord On Sat, 21 Oct 2006 12:56:31 -0700, "RagDyeR" wrote: Well Gord, with no intention of implying that I'm well versed in mathematics, I just happen to take a liking to this "Arithmetic Series". A year or so ago, Dana put me on to a link (which I can't find at the moment) that described how Gauss formulated this "truth". In words it's: <<<"The count of sequential numbers times the average of the first and last number." =A1*(A1+1)/2 So, the formula I posted is supposed to be "intuitive" for *ALL* possibilities. Here, the OP's request was for a sum of a series starting (or ending) with 1, therefore, the last (or first) number in this series is the *actual* amount (count) of numbers in the sequential series, so your A1^2 works. BUT ... what if the we're looking for, say 5 to 15? A1 = 15 B1 = 5 In the original formula, A1 now has to become (A1-B1+1) And the balance becomes (A1+B1)/2 So we now have: =(A1-B1+1)*(A1+B1)/2 Taking it a step further, there's *no* stipulation that the sequential series must have an increment or decrement constant of *1*. Let's take the same 5 to 15, but here we want an increment constant of 5, the SUM of the sequential series of 5, 10, &15. A1 = 15 B1 = 5 C1 = 5 =((A1-B1)/C1+1)*(A1+B1)/2 So, that's why: <<<"The count of sequential numbers times the average of the first and last number." =A1*(A1+1)/2 Is probably easier to relate to. Gord Dibben MS Excel MVP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help With Excel Formulas | New Users to Excel | |||
Multiple Excel versions. | Excel Discussion (Misc queries) | |||
Count consecutive dates only | Excel Discussion (Misc queries) | |||
How to make Excel 2003, not IE 6, open Excel files from a web? | Excel Discussion (Misc queries) | |||
excel should have a function to count sheets | Excel Worksheet Functions |