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.

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.

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.

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.

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.

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.

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

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

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.

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.

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.

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

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

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