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 range based on cell value
- Enter numbers from 1 to 10 into the first column.
- In cell C1 enter the phrase – Number.
- In cell D1 enter the phrase – Result.
- Enter the number between 1 and 1048576 into cell C2.
- 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.