Monthly Change Using Nested IFs?

[GJP]

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

 

[PCLIVE]

Gives an example of your current formula attempt.

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[ryguy7272]

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

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

Regards,
Ryan--

 

[GJP]

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