 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / October 2006in 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. What is the something that you want done to the digit entered?  SignatureBrevity is the soul of wit. > 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. This should work for you: =A1*(A1+1)/2 SignatureHTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- >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. Yup....as soon as I posted, my "math brain" belatedly engaged and reminded me of that formula. *********** Regards, Ron XL2002, WinXP > This should work for you: > [quoted text clipped - 6 lines] > > 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. I think this would work mo' bettah. =(A1^2+A1)/2 >This should work for you: > >=A1*(A1+1)/2 Gord Dibben MS Excel MVP oops!! Not better, just the same. Sorry RD >I think this would work mo' bettah. > [quoted text clipped - 5 lines] > >Gord Dibben MS Excel MVP Gord Dibben MS Excel MVP 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. SignatureRegards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- oops!! Not better, just the same. Sorry RD >I think this would work mo' bettah. > [quoted text clipped - 8 lines] > >Gord Dibben MS Excel MVP Gord Dibben MS Excel MVP 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 >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". [quoted text clipped - 43 lines] > >Is probably easier to relate to. Gord Dibben MS Excel MVP Found that link! Here it is: http://mathworld.wolfram.com/ArithmeticSeries.html SignatureRegards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- 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 >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". [quoted text clipped - 43 lines] > >Is probably easier to relate to. Gord Dibben MS Excel MVP 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 > 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. Carl, In A2, use the formula =SUMPRODUCT(ROW(INDIRECT("1:" & A1))*1) HTH, Bernie MS Excel MVP >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. 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 ? > Carl, > [quoted text clipped - 12 lines] > > 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. 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. > 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 ? [quoted text clipped - 20 lines] >> > need that function, I just need A2 to spit out what I mentioned >> > already. For the bricks, You are right David...but as u said,,,in the real world....years are not treated for such logical application.... > One brick goes on top of two bricks, across the join. > Two bricks go on top of three bricks across the joins etc. [quoted text clipped - 27 lines] > >> > need that function, I just need A2 to spit out what I mentioned > >> > already. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.