in Excel: how can I make excel count 5 as 15 (5+4+3+2+1)

C

Carl

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.
 
R

Ron Coderre

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
 
B

Bernie Deitrick

Carl,

In A2, use the formula

=SUMPRODUCT(ROW(INDIRECT("1:" & A1))*1)

HTH,
Bernie
MS Excel MVP
 
R

Ron Coderre

Yup....as soon as I posted, my "math brain" belatedly engaged and reminded me
of that formula.


***********
Regards,
Ron

XL2002, WinXP
 
R

romelsb

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 ?
 
D

David F Cox

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.
 
R

romelsb

For the bricks, You are right David...but as u said,,,in the real
world....years are not
treated for such logical application....
 
R

RagDyeR

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

I think this would work mo' bettah.

=(A1^2+A1)/2






Gord Dibben MS Excel MVP
Click to expand...

Gord Dibben MS Excel MVP
 
G

Gord Dibben

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".

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.
Click to expand...

Gord Dibben MS Excel MVP
 
R

RagDyeR

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

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.
Click to expand...

Gord Dibben MS Excel MVP
 
Top