Help With Summing PTD Totals

  • Thread starter Thread starter Don Hoffman
  • Start date Start date
D

Don Hoffman

I need assistance on creating formulas that can be
changed by altering a variable within the formula.
Details:

My worksheet is a P&L that contains projected & actual
results that are identified by month starting in colums S
(Jan) through AD (Dec).

I would like to have a formula in column A that
summarizes actual PTD results. In January, for example
it would be equal to the value found in Column S. In
Feb, it would be equal to the sum of the values found in
S & T; in March, it would be equal to the sum of the
values found in S, T and U.

I can obviously do this by changing the values in all the
formulas. For example in Feb the PTD formula would be SUM
(S1:T1). In March I would change the PTD formulat to be
SUM(S1:U1) But this approach requires that I change all
the PTD formulas each month.

How do I set up a PTD formula that will allow me to
change the calculation based upon changing a single
variable. For example, if I want PTD in Feb, I enter a
value of 3 in a particular cell; in March I change the
same variable to 3, and all the formulas would be
automatically adjusted.

Any ideas?

Don






and in colum B that summarizes the entire years outlook.
I can do this, by continually adjusting the formula



sales by categoryhewill allow me to change the
 
Don,

In Jan set-up a formula of

=SUM($S1:S1)

and copy across. Only the second part updates

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
One way:

Put your desired number of months (e.g., 2 for February, 3 for March) in
a cell, say B1. Then your PTD formula would be

=SUM(OFFSET(S1,,,1,B1))
 
Don
You can use the INDIRECT function to do this. INDIRECT turns a text string into a cell reference. The following formula takes the letter you enter in cell X1 and makes it part of the string. Let's say you enter "U" in X1

=SUM(INDIRECT("S1:"&X1&"1")

evalutes as: =SUM(INDIRECT("S1:"&"U"&"1")
evalutes as: =SUM(INDIRECT("S1:U1")
evalutes as: =SUM(S1:U1

In regard to Bob Phillips reply, look up absolute and relative reference styles in the help files for more information. Or go to Chip Pearson's site

http://www.cpearson.com/excel/relative.ht

Good Luck
Mark Graesse
(e-mail address removed)
----- Don Hoffman wrote: ----

I need assistance on creating formulas that can be
changed by altering a variable within the formula.
Details:

My worksheet is a P&L that contains projected & actual
results that are identified by month starting in colums S
(Jan) through AD (Dec)

I would like to have a formula in column A that
summarizes actual PTD results. In January, for example
it would be equal to the value found in Column S. In
Feb, it would be equal to the sum of the values found in
S & T; in March, it would be equal to the sum of the
values found in S, T and U

I can obviously do this by changing the values in all the
formulas. For example in Feb the PTD formula would be SU
(S1:T1). In March I would change the PTD formulat to be
SUM(S1:U1) But this approach requires that I change all
the PTD formulas each month.

How do I set up a PTD formula that will allow me to
change the calculation based upon changing a single
variable. For example, if I want PTD in Feb, I enter a
value of 3 in a particular cell; in March I change the
same variable to 3, and all the formulas would be
automatically adjusted

Any ideas

Do






and in colum B that summarizes the entire years outlook.
I can do this, by continually adjusting the formul



sales by categoryhewill allow me to change the
 
Give this a look:
=SUMIF(S1:AD1,"<="&TODAY(),S3:AD3)
or perhaps this:
=SUM(OFFSET(S3,0,0,1,MONTH(NOW())))
 
Back
Top