% change in the last two cells in a row
 

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?


--
chewmanfoo
------------------------------------------------------------------------
chewmanfoo's Profile: http://www.excelforum.com/member.php...o&userid=27112
View this thread: http://www.excelforum.com/showthread...hreadid=466251

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 a 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.


"chewmanfoo" wrote:


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?


--
chewmanfoo
------------------------------------------------------------------------
chewmanfoo's Profile: http://www.excelforum.com/member.php...o&userid=27112
View this thread: http://www.excelforum.com/showthread...hreadid=466251

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

--
Don Guillett
SalesAid Software

"chewmanfoo" wrote
in message ...

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?


--
chewmanfoo
------------------------------------------------------------------------
chewmanfoo's Profile:
http://www.excelforum.com/member.php...o&userid=27112
View this thread: http://www.excelforum.com/showthread...hreadid=466251

That formula works fine for columns A-L, but my formula needs to be
C5-N5, so I can't figure out how to transpose it. I think one of the
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,
chewmanfoo


--
chewmanfoo
------------------------------------------------------------------------
chewmanfoo's Profile: http://www.excelforum.com/member.php...o&userid=27112
View this thread: http://www.excelforum.com/showthread...hreadid=466251

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



"chewmanfoo" wrote:


That formula works fine for columns A-L, but my formula needs to be
C5-N5, so I can't figure out how to transpose it. I think one of the
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,
chewmanfoo


--
chewmanfoo
------------------------------------------------------------------------
chewmanfoo's Profile: http://www.excelforum.com/member.php...o&userid=27112
View this thread: http://www.excelforum.com/showthread...hreadid=466251

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)
--
Don Guillett
SalesAid Software

"chewmanfoo" wrote
in message ...

That formula works fine for columns A-L, but my formula needs to be
C5-N5, so I can't figure out how to transpose it. I think one of the
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,MATC
H(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,
chewmanfoo


--
chewmanfoo
------------------------------------------------------------------------
chewmanfoo's Profile:
http://www.excelforum.com/member.php...o&userid=27112
View this thread: http://www.excelforum.com/showthread...hreadid=466251

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

chewmanfoo wrote:
That formula works fine for columns A-L, but my formula needs to be
C5-N5, so I can't figure out how to transpose it. I think one of the
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,
chewmanfoo