% change in the last two cells in a row

C

chewmanfoo

Genii,

I need a way to have a cell at the end of a row that shows the % change
of the last two cells (with data in them) in a row. Imagine if you had
sales figures in a row, one column for each month. I need a column on
the right with the % change between the last two cells in that row -
like "Sales was up 21.6% from March to April". This formula has to
automagically adjust, only giving the % change opf the last two cells
in the row with data in them.

Does that make sense? Is it possible?

How is that done?
 
D

Duke Carey

assuming you are dealing with 12 months of data in row 1 - put this formula
in M1

=OFFSET(M1,0,MATCH(9.99999999999999E+307,A1:L1)-COLUMN(M1))/OFFSET(M1,0,MATCH(9.99999999999999E+307,A1:L1)-COLUMN(M1)-1)-1

The issues with this formula are: 1) any text values in cells A1:L1 will
give you an error, 2) the formula finds the LAST value in the 12 cells and
divides it by the cell to the immediate left. If that cell is empty - divide
by zero error.
 
D

Don Guillett

one way
=OFFSET(H2,MATCH(9999999,H2:H21)-1,0)/OFFSET(H2,MATCH(999999,H2:H21)-2,0)
 
C

chewmanfoo

That formula works fine for columns A-L, but my formula needs to b
C5-N5, so I can't figure out how to transpose it. I think one of th
values in the formula specifies an offset, but which one.

I'm using this formula:

=OFFSET(O5,0,MATCH(9.99999999999999E+307,C5:N5)-COLUMN(O5))/OFFSET(O5,0,MATCH(9.99999999999999E+307,C5:N5)-COLUMN(O5)-1)-1

I spent a half an hour in Excel Help trying to figure this out, nogo.

Can you tell me how to transpose it?

TIA,
chewmanfo
 
D

Duke Carey

This works in row 5

=OFFSET(C5,0,MATCH(9.99999999999999E+307,C5:N5)-1)/OFFSET(C5,0,MATCH(9.99999999999999E+307,C5:N5)-2)-1
 
D

Don Guillett

The offset function uses the ROW first and then the COLUMN. So, modify the
idea to.

=OFFSET(J2,0,MATCH(99999999999,J2:O2)-1)/
OFFSET(J2,0,MATCH(99999999999,J2:O2)-2)
 
A

Aladin Akyurek

The last numeric value from C5:N5...

=LOOKUP(9.99999999999999E+307,C5:N5)

The next-to-last numeric value from C5:N5

=LOOKUP(9.99999999999999E+307,C5:INDEX(C5:N5,MATCH(9.99999999999999E+307,C5:N5)-1))
 
Top