Automatic expansion of formula

[Ibouwicked]

Hello!

I need the value in one cell to update if another value is added in a cell not currently in my formula but in the next column. For example:

B10 = (O1/B1)^(1/13)-1

I would like excel to automatically update the fomula to "B10 = (P1/B1)^(1/14)-1 if I add a value to P1.

This is only one of the instances where i would love the same functionality as it would be very efficient! Is this possible?

Thanks for any help! :)

P.S I'm using a mac running MacOS Catalina

[#2]

@Ibouwicked Also, I'm calculating compound average growth rates if that makes any difference!

[#3]

@Ibouwicked Not sure I follow everything, but you cannot dynamically change a formula in one cell based on the content of another. However, you can have the formula check if there is something in the other cell and then use its value in stead.

Your formula in B10 could then be something like this:

=IF(ISBLANK(P1),(O1/B1)^(1/13)-1,(P1/B1)^(1/14)-1)