Range Based on Cell Value in Excel

Calculating ranges is easy. But have you ever tried to calculate a range based on the number in a specific cell?

In this lesson, I’ll show you a formula where you make such a reference.

Calculate the range based on cell value

  1. Enter numbers from 1 to 10 into the first column.
  2. In cell C1 enter the phrase – Number.
  3. In cell D1 enter the phrase – Result.
  4. Enter the number between 1 and 1048576 into cell C2.
  5. Insert the following code into cell B2.

This is our result.

I told you to enter the value between 1 and 1048576 because these are the first and the last row in Excel. If you enter a different value, text, or leave a blank cell, it will return the #REF! error.

You can handle this exception by modifying the formula.

Now, if you enter the number outside this range the exception will be handled.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.