Division formula in excel

Question

Sample files I have looked through the web and still can't find a solution. The best is this thread -https://superuser.com/questions/1083082/division-formula-in-excel

But the fundamental issues remain unresolved. How can the denominator of a division formula be an unchanging reference while yet being dynamic?

Answer 1

You may use this Array formula in your denominator to pull the value out as the ROW number in which it is found fluctuates in order to have your denominator "follow" the last cell value in Column B:

=INDEX(B$1:B$25,MAX(IF(B$1:B$25>0,ROW(B$1:B$25),"")),1)

This formula can be varied in many ways if I did not understand your intent. Enter with Ctrl, Shift, and Enter.