Can a Google Sheets cell value adjust dynamically based on another cell's number?

Other Questions Google Sheets
1

Hey everyone! I’m trying to set up a cool dynamic system in my Google sheet. Here’s what I want:

I’ve got two cells, let’s call them A and B. Cell A has a number that changes. I want cell B to react to cell A’s value.

Here’s the tricky part:

  • If cell A is over 20, I want cell B to go up by 1,000 for each point above 20
  • If cell A is under 20, I want cell B to go down by 1,000 for each point below 20

For example:

  • If A is 27, B should be 57,000
  • If A drops to 13, B should become 43,000

Starting value for cell B would be 50,000.

I’ve been scratching my head over this for a while. Any ideas on how to make this work? I’m open to trying different approaches. Thanks in advance for your help!

2

I’ve dealt with a similar situation in my work spreadsheets. Here’s a more flexible approach that might help:

Create a helper column, let’s say C. In C1, put this formula:
=IF(A1>20, A1-20, IF(A1<20, A1-20, 0))

This calculates the difference from 20, positive or negative.

Then in B1, use:
=50000 + C1*1000

This setup allows you to easily adjust the pivot point (currently 20) or the increment (currently 1000) by changing just one cell, making your sheet more adaptable for future needs.

It also makes the logic easier to follow for others who might use your sheet later on. Hope this helps!

3

hey emma, try this:

=50000+IF(A1>20,(A1-20)*1000,IF(A1<20,(A1-20)*1000,0))

it’ll do exactly what u want. plug it into B1 and ur good to go! lemme know if u need any help :slight_smile:

4

This solution can be implemented with a straightforward formula in Google Sheets. You can set cell B to:

=50000 + (A1 - 20) * 1000

This equation uses 50,000 as the starting value and adjusts the result by multiplying the difference from 20 by 1,000. When A1 is greater than 20, the positive difference adds to the base value, and when A1 is less than 20, the negative difference subtracts, giving you the desired dynamic adjustment. The formula will automatically update as the value in cell A1 changes.