Formulas that refer to a cell in the last row
I have the following two functions in the following cells on my worksheet.
AJ10 =IF(SUM(AB7:AG7)<0,(E47+SUM(AB7:AG7))/(C47+SUM(AB4:AG4)),"") AJ17 =IF(AB15<"",IF(AB14<"",(((AB15+C47)*AB14)-E47)/AB15,""),"") The first formula uses C47 and the second uses C47 and E47. These cells were the last row of my sheet when I made them. However this row changes and row 57 is now the last row. I need help setting up my formulas so they update accordingly. |
Formulas that refer to a cell in the last row
Try these formulas and see if they work...
AJ10: =IF(SUM(AB7:AG7)<0,(LOOKUP(2,1/(E$1:E$65535<""),E:E)+SUM(AB7:AG7))/(LOOKUP(2,1/(C$1:C$65535<""),C:C)+SUM(AB4:AG4)),"") AJ17: =IF(AB15<"",IF(AB14<"",(((AB15+LOOKUP(2,1/(C$1:C$65535<""),C:C))*AB14)-LOOKUP(2,1/(E$1:E$65535<""),E:E))/AB15,""),"") where I used this to get the contents or the last cell in the specified column (just change the Column A references)... LOOKUP(2,1/(A$1:A$65535<""),A:A) Rick "JBoyer" wrote in message ... I have the following two functions in the following cells on my worksheet. AJ10 =IF(SUM(AB7:AG7)<0,(E47+SUM(AB7:AG7))/(C47+SUM(AB4:AG4)),"") AJ17 =IF(AB15<"",IF(AB14<"",(((AB15+C47)*AB14)-E47)/AB15,""),"") The first formula uses C47 and the second uses C47 and E47. These cells were the last row of my sheet when I made them. However this row changes and row 57 is now the last row. I need help setting up my formulas so they update accordingly. |
Formulas that refer to a cell in the last row
Try these:
=IF(SUM(AB7:AG7)<0,(LOOKUP(99^99,E:E)+SUM(AB7:AG7 ))/(LOOKUP(99^99,C:C)+SUM(AB4:AG4)),"") =IF(AB15<"",IF(AB14<"",(((AB15+LOOKUP(99^99,C:C) )*AB14)-LOOKUP(99^99,E:E))/AB15,""),"") -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "JBoyer" wrote in message ... I have the following two functions in the following cells on my worksheet. AJ10 =IF(SUM(AB7:AG7)<0,(E47+SUM(AB7:AG7))/(C47+SUM(AB4:AG4)),"") AJ17 =IF(AB15<"",IF(AB14<"",(((AB15+C47)*AB14)-E47)/AB15,""),"") The first formula uses C47 and the second uses C47 and E47. These cells were the last row of my sheet when I made them. However this row changes and row 57 is now the last row. I need help setting up my formulas so they update accordingly. |
Formulas that refer to a cell in the last row
Thanks Rick! Yes they both work perfect.
"Rick Rothstein (MVP - VB)" wrote: Try these formulas and see if they work... AJ10: =IF(SUM(AB7:AG7)<0,(LOOKUP(2,1/(E$1:E$65535<""),E:E)+SUM(AB7:AG7))/(LOOKUP(2,1/(C$1:C$65535<""),C:C)+SUM(AB4:AG4)),"") AJ17: =IF(AB15<"",IF(AB14<"",(((AB15+LOOKUP(2,1/(C$1:C$65535<""),C:C))*AB14)-LOOKUP(2,1/(E$1:E$65535<""),E:E))/AB15,""),"") where I used this to get the contents or the last cell in the specified column (just change the Column A references)... LOOKUP(2,1/(A$1:A$65535<""),A:A) Rick "JBoyer" wrote in message ... I have the following two functions in the following cells on my worksheet. AJ10 =IF(SUM(AB7:AG7)<0,(E47+SUM(AB7:AG7))/(C47+SUM(AB4:AG4)),"") AJ17 =IF(AB15<"",IF(AB14<"",(((AB15+C47)*AB14)-E47)/AB15,""),"") The first formula uses C47 and the second uses C47 and E47. These cells were the last row of my sheet when I made them. However this row changes and row 57 is now the last row. I need help setting up my formulas so they update accordingly. |
All times are GMT +1. The time now is 12:18 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com