Monthly Change Using Nested IFs?

G

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
 
S

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
 
R

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--
 
G

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top