Excel Not Evaluating Cell

[babeneker]

I'm trying to get a formula to work, but it appears Excel refuses to evaluate a cell that has a variable value in it. In searching for solutions online, I have tried changing the formatting for cells and it hasn't worked. I also tried changing my formula thinking it may be an issue with that. Nope.

This is my sheet with the highlighted cells being the ones that I'm trying to get to calculate the formula below. They all (except for one) show the same result.

Here is what the formula looks like:

It appears that Excel refuses to evaluate the value in cell B29.

In this example, cell B32 is correctly evaluated. Coming back with no value for B29. I also tried changing the formula to =B32/B29^1. Same problem.

Any ideas how I can fix this?

 

[Cubist]

When posting, please include row and column headers. We don't know which cells you're referring to. Which column is B?

 

[MARK858]

I get the picture you posted on the first click of Evaluate.
on the second click of Evaluate I get

on the third click I get

 

[babeneker]

When posting, please include row and column headers. We don't know which cells you're referring to. Which column is B?

I'm sorry, Cubist. Here is an updated view.

 

[babeneker]

I get the picture you posted on the first click of Evaluate.
on the second click of Evaluate I get
View attachment 120359
on the third click I get

View attachment 120360

My mistake. I only clicked once and thought it was going to show me everything. After clicking more it does evaluate properly. The carrot method (B32/B29^2) is one that I like better as it breaks it down step-by-step. But it still returns the same value. It can't be the same value with different values and powers. I'm not understanding what's going on.

 

[babeneker]

I apologize. I should flesh out more of what I'm doing with the formulas in the Discounted column.

Year 2 (C32) would have a formula of B32/B29^1
Year 3 (C33) would have a formula of B33/B29^2
Year (C34) would have a formula of B34/B29^3

Each year going down will increase the exponent by 1 to represent a year passing.

 

[Cubist]

In C32, try:

=B32/$B$29^ROWS($B$32:B32)

 

[babeneker]

In C32, try:

=B32/$B$29^ROWS($B$32:B32)

That worked! I'm not sure what the rows do, but it makes it behave. Thank you so much!

 

[Cubist]

That worked! I'm not sure what the rows do, but it makes it behave. Thank you so much!

You're welcome.

The ROWS return number of rows given the range reference. It increments by 1 as you copy down.
ROWS($B$32:B32) =1
ROWS($B$32:B33) =2
.... and so on...

 

[babeneker]

You're welcome.

The ROWS return number of rows given the range reference. It increments by 1 as you copy down.
ROWS($B$32:B32) =1
ROWS($B$32:B33) =2
.... and so on...

Oooh, I just noticed the detail in your post (range always starts at 32, which makes sense with your explanation). It's back to returning the same values. I'm not sure how this is happening... unless the math from my source is incorrect. I will check into that.