Mar 24 2020 08:18 AM
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!
Mar 24 2020 08:53 AM
@flynn05 A mockup of the calculation attached.
Mar 24 2020 11:20 AM
SolutionHey @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.
Mar 24 2020 11:34 AM
Mar 24 2020 11:42 AM - edited Mar 24 2020 02:04 PM
@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!
Mar 25 2020 11:14 AM
Mar 24 2020 11:20 AM
SolutionHey @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.