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?

Dec 26, 2022 in Others 225 views

## 1 answer to this question.

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.

• 63,420 points

## INDEX formula in Excel, Top 10, repeats previous value

Try this formula in cell W4: =IF(V3=V4,INDEX(INDIRECT("I"&MATCH(W3,I:I,0)+1&":I26"),MATCH(V4,INDIRECT("R"&MATCH(W3,I:I,0)+1&":R26"),0)),INDEX(\$I\$2:\$I\$26,MATCH(V4,\$R\$2:\$R\$26,0))) The calculation ...READ MORE

## Excel formula for searching two text in one cell and return values based on the result

You can include a second IF within ...READ MORE

## Shortcut to Apply a Formula to an Entire Column in Excel

Try double-clicking on the bottom right hand ...READ MORE

## Formula to convert KG,MB,GB,TB to GB in excel

Use this array formula: =LEFT(A1,MIN(IFERROR(FIND({"KB","MB","GB","TB"},A1),1E+99))-1)/INDEX({1048576,1024,1,0.0009765625},MATCH(RIGHT(A1,2),{"KB","MB","GB","TB"},0)) & "GB" Being an ...READ MORE

## Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

## Remove formulas from all worksheets in Excel using VBA

Try this : Option Explicit Sub test1() ...READ MORE

## Calculate monthly average from daily data without PivotTable

Assuming you have the months in column D enter ...READ MORE

## Automate compound annual growth rate (CAGR) calculation

The following PowerPivot DAX formulas worked for ...READ MORE