Factorial (like =FACT) function?

[mr tom]

A factorial expression, e.g. 5! is evaluated 5*4*3*2*1.

In Excel, this can be expressed as =FACT(5)

I want to do something similar, but a little different: 5+4+3+2+1

E.g. Year to date could be results for June + May + April + March + February
+ January, where Month(TODAY) gives 06 as June and then simply recalculates
the month sensitive formulas based on each month below this number, returning
the total of all evaluations.

Ideally I'd like to manage this within a formula, without resorting to VBA.
Any ideas?

Tom.

P.S. Many thanks for any attempts - regardless of whether they solve my
problem!

[Chip Pearson]

Tom,

I'm not entirely clear on what you want to do, but you can add
the numbers between 1 and N with the array formula

=SUM(ROW(INDIRECT("1:"&A1)))

where A1 contains N.

Since this is an array formula, you must press CTRL+SHIFT+ENTER
rather than just ENTER when you first entire the formula and
whenever you edit it later. If you do this correctly, Excel will
display the formula in curly braces {}.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"mr tom" <mr-tom at mr-tom.co.uk.(donotspam)> wrote in message
news:[email protected]...
>A factorial expression, e.g. 5! is evaluated 5*4*3*2*1.
>
> In Excel, this can be expressed as =FACT(5)
>
> I want to do something similar, but a little different:
> 5+4+3+2+1
>
> E.g. Year to date could be results for June + May + April +
> March + February
> + January, where Month(TODAY) gives 06 as June and then simply
> recalculates
> the month sensitive formulas based on each month below this
> number, returning
> the total of all evaluations.
>
> Ideally I'd like to manage this within a formula, without
> resorting to VBA.
> Any ideas?
>
> Tom.
>
> P.S. Many thanks for any attempts - regardless of whether they
> solve my
> problem!

[Ron Coderre]

Try this:

For a value in A1

This formula returns the sum of all numbers from 1 thru the value in A1
B1: =(A1+1)*(A1/2)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"mr tom" wrote:

> A factorial expression, e.g. 5! is evaluated 5*4*3*2*1.
>
> In Excel, this can be expressed as =FACT(5)
>
> I want to do something similar, but a little different: 5+4+3+2+1
>
> E.g. Year to date could be results for June + May + April + March + February
> + January, where Month(TODAY) gives 06 as June and then simply recalculates
> the month sensitive formulas based on each month below this number, returning
> the total of all evaluations.
>
> Ideally I'd like to manage this within a formula, without resorting to VBA.
> Any ideas?
>
> Tom.
>
> P.S. Many thanks for any attempts - regardless of whether they solve my
> problem!

[mr tom]

Not quite.

Another poorly asked question on my part!

Let's say you have a data source where one column is date. Another column
might be product. Another might be amount.

At the end of the month, you'd sum all the amounts where the product meets
certain criterea and MONTH(date entered in column) = MONTH(TODAY()).

This is how it presently works.

If I then wanted year to date, I could simply sum all cases for that year,
but let's say the data quality is not what it could be and having a series of
months that don't add up to the year to date total is a little embarrassing.

My alternative was a function that does
{=SUM(IF(Product="x"),IF(MONTH(DateEntered)=MONTH(TODAY()),Amounts,0),0)}
[the current formula or close to it]
but does this for the current month and every previous month until Month=0.

Am I clutching at straws?

"Ron Coderre" wrote:

> Try this:
>
> For a value in A1
>
> This formula returns the sum of all numbers from 1 thru the value in A1
> B1: =(A1+1)*(A1/2)
>
> Does that help?
> ***********
> Regards,
> Ron
>
> XL2002, WinXP
>
>
> "mr tom" wrote:
>
> > A factorial expression, e.g. 5! is evaluated 5*4*3*2*1.
> >
> > In Excel, this can be expressed as =FACT(5)
> >
> > I want to do something similar, but a little different: 5+4+3+2+1
> >
> > E.g. Year to date could be results for June + May + April + March + February
> > + January, where Month(TODAY) gives 06 as June and then simply recalculates
> > the month sensitive formulas based on each month below this number, returning
> > the total of all evaluations.
> >
> > Ideally I'd like to manage this within a formula, without resorting to VBA.
> > Any ideas?
> >
> > Tom.
> >
> > P.S. Many thanks for any attempts - regardless of whether they solve my
> > problem!

[Ron Coderre]

Maybe something like this:

For a data list in A1:C100
Col_A contains dates and a column title in A1
Col_B contains products and a column title in B1
Col_C contains amounts and a column title in C1

This formula sums all of YTD amounts (through the current month) where the
Prod="X"

D1:
=SUMPRODUCT((B2:B100="x")*(YEAR(A2:A100)=YEAR(TODAY()))*(MONTH(A2:A100)<=MONTH(TODAY()))*C2:C100)

Note: in case text wrap impacts the display, there are no spaces in that
formula.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"mr tom" wrote:

> Not quite.
>
> Another poorly asked question on my part!
>
> Let's say you have a data source where one column is date. Another column
> might be product. Another might be amount.
>
> At the end of the month, you'd sum all the amounts where the product meets
> certain criterea and MONTH(date entered in column) = MONTH(TODAY()).
>
> This is how it presently works.
>
> If I then wanted year to date, I could simply sum all cases for that year,
> but let's say the data quality is not what it could be and having a series of
> months that don't add up to the year to date total is a little embarrassing.
>
> My alternative was a function that does
> {=SUM(IF(Product="x"),IF(MONTH(DateEntered)=MONTH(TODAY()),Amounts,0),0)}
> [the current formula or close to it]
> but does this for the current month and every previous month until Month=0.
>
> Am I clutching at straws?
>
> "Ron Coderre" wrote:
>
> > Try this:
> >
> > For a value in A1
> >
> > This formula returns the sum of all numbers from 1 thru the value in A1
> > B1: =(A1+1)*(A1/2)
> >
> > Does that help?
> > ***********
> > Regards,
> > Ron
> >
> > XL2002, WinXP
> >
> >
> > "mr tom" wrote:
> >
> > > A factorial expression, e.g. 5! is evaluated 5*4*3*2*1.
> > >
> > > In Excel, this can be expressed as =FACT(5)
> > >
> > > I want to do something similar, but a little different: 5+4+3+2+1
> > >
> > > E.g. Year to date could be results for June + May + April + March + February
> > > + January, where Month(TODAY) gives 06 as June and then simply recalculates
> > > the month sensitive formulas based on each month below this number, returning
> > > the total of all evaluations.
> > >
> > > Ideally I'd like to manage this within a formula, without resorting to VBA.
> > > Any ideas?
> > >
> > > Tom.
> > >
> > > P.S. Many thanks for any attempts - regardless of whether they solve my
> > > problem!

[mr tom]

Yes - that's exactly it. I'll stick with my previous style of formula but
you're absolutely right - I deserve to be taken out and shot for not thinking
of a <=.

I've clearly overcomplicated things - your solution will work a treat.

Many thanks,

Tom.

"Ron Coderre" wrote:

> Maybe something like this:
>
> For a data list in A1:C100
> Col_A contains dates and a column title in A1
> Col_B contains products and a column title in B1
> Col_C contains amounts and a column title in C1
>
> This formula sums all of YTD amounts (through the current month) where the
> Prod="X"
>
> D1:
> =SUMPRODUCT((B2:B100="x")*(YEAR(A2:A100)=YEAR(TODAY()))*(MONTH(A2:A100)<=MONTH(TODAY()))*C2:C100)
>
> Note: in case text wrap impacts the display, there are no spaces in that
> formula.
>
> Does that help?
> ***********
> Regards,
> Ron
>
> XL2002, WinXP
>
>
> "mr tom" wrote:
>
> > Not quite.
> >
> > Another poorly asked question on my part!
> >
> > Let's say you have a data source where one column is date. Another column
> > might be product. Another might be amount.
> >
> > At the end of the month, you'd sum all the amounts where the product meets
> > certain criterea and MONTH(date entered in column) = MONTH(TODAY()).
> >
> > This is how it presently works.
> >
> > If I then wanted year to date, I could simply sum all cases for that year,
> > but let's say the data quality is not what it could be and having a series of
> > months that don't add up to the year to date total is a little embarrassing.
> >
> > My alternative was a function that does
> > {=SUM(IF(Product="x"),IF(MONTH(DateEntered)=MONTH(TODAY()),Amounts,0),0)}
> > [the current formula or close to it]
> > but does this for the current month and every previous month until Month=0.
> >
> > Am I clutching at straws?
> >
> > "Ron Coderre" wrote:
> >
> > > Try this:
> > >
> > > For a value in A1
> > >
> > > This formula returns the sum of all numbers from 1 thru the value in A1
> > > B1: =(A1+1)*(A1/2)
> > >
> > > Does that help?
> > > ***********
> > > Regards,
> > > Ron
> > >
> > > XL2002, WinXP
> > >
> > >
> > > "mr tom" wrote:
> > >
> > > > A factorial expression, e.g. 5! is evaluated 5*4*3*2*1.
> > > >
> > > > In Excel, this can be expressed as =FACT(5)
> > > >
> > > > I want to do something similar, but a little different: 5+4+3+2+1
> > > >
> > > > E.g. Year to date could be results for June + May + April + March + February
> > > > + January, where Month(TODAY) gives 06 as June and then simply recalculates
> > > > the month sensitive formulas based on each month below this number, returning
> > > > the total of all evaluations.
> > > >
> > > > Ideally I'd like to manage this within a formula, without resorting to VBA.
> > > > Any ideas?
> > > >
> > > > Tom.
> > > >
> > > > P.S. Many thanks for any attempts - regardless of whether they solve my
> > > > problem!

[Ron Coderre]

Thanks for the feed back...I'm glad you got that to work for you.

BTW...this newsgroup hardly ever shoots anybody any more. <vbg>

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

XL2002, WinXP


"mr tom" wrote:

> Yes - that's exactly it. I'll stick with my previous style of formula but
> you're absolutely right - I deserve to be taken out and shot for not thinking
> of a <=.
>
> I've clearly overcomplicated things - your solution will work a treat.
>
> Many thanks,
>
> Tom.
>
> "Ron Coderre" wrote:
>
> > Maybe something like this:
> >
> > For a data list in A1:C100
> > Col_A contains dates and a column title in A1
> > Col_B contains products and a column title in B1
> > Col_C contains amounts and a column title in C1
> >
> > This formula sums all of YTD amounts (through the current month) where the
> > Prod="X"
> >
> > D1:
> > =SUMPRODUCT((B2:B100="x")*(YEAR(A2:A100)=YEAR(TODAY()))*(MONTH(A2:A100)<=MONTH(TODAY()))*C2:C100)
> >
> > Note: in case text wrap impacts the display, there are no spaces in that
> > formula.
> >
> > Does that help?
> > ***********
> > Regards,
> > Ron
> >
> > XL2002, WinXP
> >
> >
> > "mr tom" wrote:
> >
> > > Not quite.
> > >
> > > Another poorly asked question on my part!
> > >
> > > Let's say you have a data source where one column is date. Another column
> > > might be product. Another might be amount.
> > >
> > > At the end of the month, you'd sum all the amounts where the product meets
> > > certain criterea and MONTH(date entered in column) = MONTH(TODAY()).
> > >
> > > This is how it presently works.
> > >
> > > If I then wanted year to date, I could simply sum all cases for that year,
> > > but let's say the data quality is not what it could be and having a series of
> > > months that don't add up to the year to date total is a little embarrassing.
> > >
> > > My alternative was a function that does
> > > {=SUM(IF(Product="x"),IF(MONTH(DateEntered)=MONTH(TODAY()),Amounts,0),0)}
> > > [the current formula or close to it]
> > > but does this for the current month and every previous month until Month=0.
> > >
> > > Am I clutching at straws?
> > >
> > > "Ron Coderre" wrote:
> > >
> > > > Try this:
> > > >
> > > > For a value in A1
> > > >
> > > > This formula returns the sum of all numbers from 1 thru the value in A1
> > > > B1: =(A1+1)*(A1/2)
> > > >
> > > > Does that help?
> > > > ***********
> > > > Regards,
> > > > Ron
> > > >
> > > > XL2002, WinXP
> > > >
> > > >
> > > > "mr tom" wrote:
> > > >
> > > > > A factorial expression, e.g. 5! is evaluated 5*4*3*2*1.
> > > > >
> > > > > In Excel, this can be expressed as =FACT(5)
> > > > >
> > > > > I want to do something similar, but a little different: 5+4+3+2+1
> > > > >
> > > > > E.g. Year to date could be results for June + May + April + March + February
> > > > > + January, where Month(TODAY) gives 06 as June and then simply recalculates
> > > > > the month sensitive formulas based on each month below this number, returning
> > > > > the total of all evaluations.
> > > > >
> > > > > Ideally I'd like to manage this within a formula, without resorting to VBA.
> > > > > Any ideas?
> > > > >
> > > > > Tom.
> > > > >
> > > > > P.S. Many thanks for any attempts - regardless of whether they solve my
> > > > > problem!

[Dana DeLouis]

> I want to do something similar:... 5+4+3+2+1

If you are summing 1 to n, then maybe:
n(n+1)/2

ie 5*6/2 = 15
--
HTH. :>)
Dana DeLouis
Windows XP, Office 2003


"mr tom" <mr-tom at mr-tom.co.uk.(donotspam)> wrote in message
news:[email protected]...
>A factorial expression, e.g. 5! is evaluated 5*4*3*2*1.
>
> In Excel, this can be expressed as =FACT(5)
>
> I want to do something similar, but a little different: 5+4+3+2+1
>
> E.g. Year to date could be results for June + May + April + March +
> February
> + January, where Month(TODAY) gives 06 as June and then simply
> recalculates
> the month sensitive formulas based on each month below this number,
> returning
> the total of all evaluations.
>
> Ideally I'd like to manage this within a formula, without resorting to
> VBA.
> Any ideas?
>
> Tom.
>
> P.S. Many thanks for any attempts - regardless of whether they solve my
> problem!