Change a field if another field is changed

[flynn05]

Hi there! Im trying to make my spreadsheet model lower my average monthly sales by 20 for each 0.05 drop in exchange rate and increase by 20 for each 0.05 increase. I can't figure out how to do this and I hope you can help, please look at the screenshot for more info, thanks!

[#2]

@flynn05 A mockup of the calculation attached.

[#3 · Accepted solution]

Hey @flynn05 

The first cell is one that you just input. So the exchange rate. Or you can use a drop down list. 

The second cell is a formula that does the math for you. It assumes a starting place. Looks like if exchange rate = 1 then the projected sales is 900. 

 I copied a picture of the formula that might be easier. But it basically starts with 900 and will do the calculation you asked for. It doent go up, so if 1 is the highest and 900 sales is the highest then you are golden. 

You can hard code the first part of the formula. So =if(I3 = 1, 900,

I3 is the reference to the cell that you will enter that is the exchange rate. 

Then it does an if not statement. If its not 1, then take 1 less the exchange rate and divide by .05. 

Anyhow. You should be good from here if you follow the picture and formula. Good luck. 

[#4]

Thanks so much for your help! It’s actually if the exchange rate = 0.7 sales = 900 but I’ll just follow the picture and what you said, thanks!

[#5]

@Savia Hi there, thanks for the response it works! I was also wondering if there is a way to make it for every 0.05 drop sales increase by 20. Thanks again!

[#6]

@flynn05 

It should. Maybe change it to add 20 rather than -20.