% change in the last two cells in a row

[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?

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


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

[Don Guillett]

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

--
Don Guillett
SalesAid Software
[email protected]
"chewmanfoo" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[chewmanfoo]

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

[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



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

[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)
--
Don Guillett
SalesAid Software
[email protected]
"chewmanfoo" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

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

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