Increase Formula Cell Selection Based on Value

[Pablo]

Not a good subject line, but here is what I'm looking for:

I have a year to date spreadsheet that compares data from multiple
years. The prior years have a full years worth of data listed by month
and a row that calculates the YTD. Every month I change the formula in
the YTD row to calculate the correct number of months. Jan-Dec are in
B1-B12 YTD is in B13 so I have formula =sum(B1:B3) for YTD through
March. I have to do this in several rows and prefer sheets that I
don't have to change, thus mess up at some point...

I have another cell that counts the number of values in the current
year and returns a number. So for March 2011, it will look at all the
months for 2011 and only see three values and return 3. In December it
will return 12. Let's say its in C1.

I would like to make the YTD formula select the number of
cells(months) it will sum based on the month counter value. So it
would be something like =sum(B1:B1+value of C1)

 

[joeu2004]

Jan-Dec are in B1-B12  YTD is in B13 so I have formula
=sum(B1:B3) for YTD through March.  I [...] prefer sheets
that I don't have to change, thus mess up at some point... [....]
So it would be something like =sum(B1:B1+value of C1)

Although we could provide a formula to do that, it should not be
necessary. Why can't you simply use =SUM(B1:B12)?

Presumably B4:B14 are empty, zero or the null string ("") for Apr
through Dec of this year. Right?

Or did I misunderstand? Are you trying to compare the sum of the same
number of months each year, namely teh number of months in the current
year?

If that is the case, I would suggest the following:
=SUM(B$1:INDEX(B$1:B$12,$C$1,0))

INDEX is better to use than INDIRECT or OFFSET because that latter two
are volatile functions, so their formulas are recalculated every time
you edit any cell in the workbook(!). That can really slow down
editing if you have a lot of these formulas.

The use of partial-absolute references (e.g. B$1 instead of B1 or $B
$1) facilitates copying the formula across the row, where each column
represents the 12-month data of each year.

But in that case, you would probably use B2:B13 or you would not use
C1 for the number of months to compare.

 

[GS]

Pablo has brought this to us :

Not a good subject line, but here is what I'm looking for:

I have a year to date spreadsheet that compares data from multiple
years. The prior years have a full years worth of data listed by month
and a row that calculates the YTD. Every month I change the formula in
the YTD row to calculate the correct number of months. Jan-Dec are in
B1-B12 YTD is in B13 so I have formula =sum(B1:B3) for YTD through
March. I have to do this in several rows and prefer sheets that I
don't have to change, thus mess up at some point...

I have another cell that counts the number of values in the current
year and returns a number. So for March 2011, it will look at all the
months for 2011 and only see three values and return 3. In December it
will return 12. Let's say its in C1.

I would like to make the YTD formula select the number of
cells(months) it will sum based on the month counter value. So it
would be something like =sum(B1:B1+value of C1)

Hi Pablo,
It sounds to me like you need to define some ColumnAbsolute-RowRelative
range names so you can collect the totals appropriately for each qtr
AND for the full year. If your spreadsheet does this for several years
of data then the formulas that use the defined name ranges would be
reusable throughout the worksheet. This would also obviate any need for
a 'counter' cell.

I'm thinking 12 rows for each month/year and 5 rows for totals. This
pattern would repeat for each year listed on the sheet.

Example:
Qtr1_Total: =SUM(Qtr1)
Qtr2_Total: =SUM(Qtr2)
Qtr3_Total: =SUM(Qtr3)
Qtr4_Total: =SUM(Qtr4)
Year_Total: =SUM(ThisYear)

 

[Pablo]

Thanks, that worked great.

I couldn't use the simple sum because there are multiple columns with
multiple years and formulas calculating the change from year to year.
So each year needs to be calculated with the year to date. The INDEX
function worked marvelously and I used it on a bunch of other cells as
well.

Thanks again.

 

[GS]

Pablo has brought this to us :

Not a good subject line, but here is what I'm looking for:

I have a year to date spreadsheet that compares data from multiple
years. The prior years have a full years worth of data listed by month
and a row that calculates the YTD. Every month I change the formula in
the YTD row to calculate the correct number of months. Jan-Dec are in
B1-B12 YTD is in B13 so I have formula =sum(B1:B3) for YTD through
March. I have to do this in several rows and prefer sheets that I
don't have to change, thus mess up at some point...

I have another cell that counts the number of values in the current
year and returns a number. So for March 2011, it will look at all the
months for 2011 and only see three values and return 3. In December it
will return 12. Let's say its in C1.

I would like to make the YTD formula select the number of
cells(months) it will sum based on the month counter value. So it
would be something like =sum(B1:B1+value of C1)

Hi Pablo,
It sounds to me like you need to define some ColumnAbsolute-RowRelative
range names so you can collect the totals appropriately for each qtr
AND for the full year. If your spreadsheet does this for several years
of data then the formulas that use the defined name ranges would be
reusable throughout the worksheet. This would also obviate any need for
a 'counter' cell.

I'm thinking 12 rows for each month/year and 5 rows for totals. This
pattern would repeat for each year listed on the sheet.

Example:
Qtr1_Total: =SUM(Qtr1)
Qtr2_Total: =SUM(Qtr2)
Qtr3_Total: =SUM(Qtr3)
Qtr4_Total: =SUM(Qtr4)
Year_Total: =SUM(ThisYear)

 

[Jim Cone]

=SUM(OFFSET(B1,0,0,C1,1))
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(Extras for Excel add-in: convenience built-in)




"Pablo" <[email protected]>
wrote in message
news:f4d0ebc3-af8a-4d2a-bf3e-20d0f60b8fd7@i35g2000prd.googlegroups.com...

 

[GS]

Not a good subject line, but here is what I'm looking for:

I have a year to date spreadsheet that compares data from multiple
years. The prior years have a full years worth of data listed by month
and a row that calculates the YTD. Every month I change the formula in
the YTD row to calculate the correct number of months. Jan-Dec are in
B1-B12 YTD is in B13 so I have formula =sum(B1:B3) for YTD through
March. I have to do this in several rows and prefer sheets that I
don't have to change, thus mess up at some point...

I have another cell that counts the number of values in the current
year and returns a number. So for March 2011, it will look at all the
months for 2011 and only see three values and return 3. In December it
will return 12. Let's say its in C1.

I would like to make the YTD formula select the number of
cells(months) it will sum based on the month counter value. So it
would be something like =sum(B1:B1+value of C1)

Hi Pablo,
It sounds to me like you need to define some ColumnAbsolute-RowRelative
range names so you can collect the totals appropriately for each qtr
AND for the full year. If your spreadsheet does this for several years
of data then the formulas that use the defined name ranges would be
reusable throughout the worksheet. This would also obviate any need for
a 'counter' cell.

I'm thinking 12 rows for each month/year and 5 rows for totals. This
pattern would repeat for each year listed on the sheet.

Example:
Qtr1_Total: =SUM(Qtr1)
Qtr2_Total: =SUM(Qtr2)
Qtr3_Total: =SUM(Qtr3)
Qtr4_Total: =SUM(Qtr4)
Year_Total: =SUM(ThisYear)

 

[Pablo]

Garry

I do need the absolute sums by the quarters, which is easy, but what
the Index function allows me to do is calculate the year to date
across all those columns (years). So if I'm half way through the 2nd
quarter say April. I can have a row that is YTD that sums my totals
for Jan-April for each year.

 

[GS]

Pablo pretended :

Garry

I do need the absolute sums by the quarters, which is easy, but what
the Index function allows me to do is calculate the year to date
across all those columns (years). So if I'm half way through the 2nd
quarter say April. I can have a row that is YTD that sums my totals
for Jan-April for each year.

I see! So I agree with you if you want to be able to do this randomly
rather than show fixed values for Qtr data.

I do something similar but my sheet layout is different (it summarizes
values/categories from source data entry sheets). I summarize by row
for data category, columns for months the data belongs to, and a column
for YTD that sums each row (and so is always current to date). Each row
of data includes a date on the 'source' sheet, and the category the
data belongs under. The date determines which month column the amount
belongs in (also on the source sheet). The month columns on the summary
sheet read from the month columns on the source sheets, for each
category entered on the source sheets. Below the summary table is a row
for month totals (and the year total). Below this row is where I also
calc QtrToDate totals for each Qtr.

The result is I never have to do a manual calc to get 'at the moment'
totals, whether by MonthToDate (MTD), QtrToDate (QTD), or YearToDate
(YTD)!

regards,