SOLVED

Change a field if another field is changed

Highlighted
Occasional Contributor

Change a field if another field is changed

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!

5 Replies
Highlighted

Re: Change a field if another field is changed

@flynn05 A mockup of the calculation attached.

Highlighted
Best Response confirmed by flynn05 (Occasional Contributor)
Solution

Re: Change a field if another field is changed

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.

Highlighted

Re: Change a field if another field is changed

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!
Highlighted

Re: Change a field if another field is changed

@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!

Highlighted

Re: Change a field if another field is changed

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