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.