calculate year-to-date total

[Blessedx3]

I hope I can explain this correctly.

I have a worksheet in which I am comparing totals between last fiscal year
and this fiscal year.

In cell A1 I have 'FY07'
In cell A2 I have 'FY08'
In cell A3 I have '7' (because January is the 7th month of our fiscal year).

In column B I have the list of our plants, 'Plant A', 'Plant B', etc

In column C I have totals for 'July FY07', in column D 'July FY08', column E
'August FY07', column F 'August FY08', etc. through column Z 'June FY08'.

In column AA I have the YTD total for FY07, column AB 'YTD FY08'.

I am ok with using the SUM feature to calculate YTD FY08 since months that
haven't come yet have a 0 as the total so SUM works perfectly.

My problem is with YTD FY07. Right now, the spreadsheet is using an IF
statement which works off of the month number in cell A3, for example:
IF(A$3=1,C1,IF(A$3=2,C1+E1, IF(A$3=3, C1+E1+G1, etc...)). This formula works
great until we get to January (month 7) since the IF statement only allows 6
IF's. I need to find a way to add the FY07 columns for YTD.

The person who made the original spreadsheet years ago is no longer here and
no one else seems to know why the sheet was set up this way. I've probably
been looking at this so much that I just can't see the forest for the trees
so I hope there is any easy fix out there, well, it doesn't necessarily have
to be easy it just has to work.

I hope I've been able to express this correctly and that it makes sense.
Any help would be greatly appreciated.

Thanks!

 

[akphidelt]

Theres probably other solutions, but the way I deal with YTD type sums in my
databases is to create a running total below the data so create a new column
and start it off in column C FY07 July

And put = C2 (if this is where your first piece of data is)

Then in E write =C2+E2

Then in F write E2 + F2

And do this all the way across

Then you can use an offset statement that picks the YTD month based on the
value in A3

So for instance the current example you would have would look like this...

In column AA write

=Offset(B3,,A3*2-1)

This is assuming you put the YTD figures in B3

 

[bpeltzer]

The IF statements don't need to be nested...
=IF(A3>=1,C1,0)+IF(A3>=2,E1,0)+IF(A3>=3,G1,0)+...+IF(A3>=12,Y1,0)