Formulas that refer to a cell in the last row

[JBoyer]

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.

 

[Rick Rothstein \(MVP - VB\)]

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

 

[RagDyer]

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

 

[JBoyer]

Thanks Rick! Yes they both work perfect.

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