Forum Discussion
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!
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.
5 Replies
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.
- 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!
