I've got a spreadsheet that tracks a number of inventory categories & values
by month. The last column calculates the Monthly Change for each row - this
month's inventory minus last month's inventory. Rather than change the
formula each month I thought I could use a nested IF based on the month
number, but ran into the limitation of 7 nested IFs, which is a problem given
that there are 12 months. Anyone have a good way to do a subtraction
calculation in a cell that will have varying column references each month?
Thanks

Gives an example of your current formula attempt.

--

"GJP" wrote in message
...
I've got a spreadsheet that tracks a number of inventory categories &
values
by month. The last column calculates the Monthly Change for each row -
this
month's inventory minus last month's inventory. Rather than change the
formula each month I thought I could use a nested IF based on the month
number, but ran into the limitation of 7 nested IFs, which is a problem
given
that there are 12 months. Anyone have a good way to do a subtraction
calculation in a cell that will have varying column references each month?
Thanks

Assuming the your sheet has each month's value in a the columns B:M then:

=LOOKUP(10^10,B2:M2)-INDEX(B2:M2,COUNT(B2:M2)-1)

or

=INDEX(B2:M2,COUNT(B2:M2))-INDEX(B2:M2,COUNT(B2:M2)-1)

will return the last value minus the second last value

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"GJP" wrote in message
...
I've got a spreadsheet that tracks a number of inventory categories &
values
by month. The last column calculates the Monthly Change for each row -
this
month's inventory minus last month's inventory. Rather than change the
formula each month I thought I could use a nested IF based on the month
number, but ran into the limitation of 7 nested IFs, which is a problem
given
that there are 12 months. Anyone have a good way to do a subtraction
calculation in a cell that will have varying column references each month?
Thanks

You are probably lacking information here, but try this:
=LOOKUP(B1,{"Jan","Feb","Mar","Apr","May","Jun","J ul","Aug","Sep","Oct","Nov","Dec"})

Or perhaps this:
=OFFSET($A$1,,MATCH(B1,{"Jan","Feb","Mar","Apr","M ay","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},0 ))

Regards,
Ryan--

--
RyGuy


"PCLIVE" wrote:

Gives an example of your current formula attempt.

--

"GJP" wrote in message
...
I've got a spreadsheet that tracks a number of inventory categories &
values
by month. The last column calculates the Monthly Change for each row -
this
month's inventory minus last month's inventory. Rather than change the
formula each month I thought I could use a nested IF based on the month
number, but ran into the limitation of 7 nested IFs, which is a problem
given
that there are 12 months. Anyone have a good way to do a subtraction
calculation in a cell that will have varying column references each month?
Thanks

Thanks to all for the assistance. After thinking about it some more I came
up with a solution that isn't too complex.

Each column has the month's text label. Under these labels I inserted the
integers 1 through 12. I then put in a box for the user to key in the
current month's integer. Right below the box is a formula that subtracts one
from the current month's integer.

To do the monthly change calculation, the formula is Hlookup(integer box
value,data table with the month's integer as the first row) - Hlookup(integer
box value - 1 cell,data table with the month's integer as the first row)

GJP

"Sandy Mann" wrote:

Assuming the your sheet has each month's value in a the columns B:M then:

=LOOKUP(10^10,B2:M2)-INDEX(B2:M2,COUNT(B2:M2)-1)

or

=INDEX(B2:M2,COUNT(B2:M2))-INDEX(B2:M2,COUNT(B2:M2)-1)

will return the last value minus the second last value

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"GJP" wrote in message
...
I've got a spreadsheet that tracks a number of inventory categories &
values
by month. The last column calculates the Monthly Change for each row -
this
month's inventory minus last month's inventory. Rather than change the
formula each month I thought I could use a nested IF based on the month
number, but ran into the limitation of 7 nested IFs, which is a problem
given
that there are 12 months. Anyone have a good way to do a subtraction
calculation in a cell that will have varying column references each month?
Thanks