Home |
Search |
Today's Posts |
#1
|
|||
|
|||
% 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 |
#2
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Simultaneously change values in multiple cells? | Excel Worksheet Functions | |||
is it possible to select and change the values of cells in a minimized excel spread sheet from vba? | Excel Worksheet Functions | |||
i change a source and the other cells wont change | Excel Worksheet Functions | |||
I Need to change reference sheet for all cells on a form | Excel Discussion (Misc queries) | |||
Change a cell's fill color dynamically? | Excel Discussion (Misc queries) |