SOLVED

Change a field if another field is changed

%3CLINGO-SUB%20id%3D%22lingo-sub-1249801%22%20slang%3D%22en-US%22%3EChange%20a%20field%20if%20another%20field%20is%20changed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1249801%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20there!%20Im%20trying%20to%20make%20my%20spreadsheet%20model%20%3CSTRONG%3Elower%3C%2FSTRONG%3E%20my%20average%20monthly%20sales%20by%20%3CSTRONG%3E20%3C%2FSTRONG%3E%20for%20each%20%3CSTRONG%3E0.05%3C%2FSTRONG%3E%20%3CSTRONG%3Edrop%3C%2FSTRONG%3E%20in%20exchange%20rate%20and%20%3CSTRONG%3Eincrease%3C%2FSTRONG%3E%20by%20%3CSTRONG%3E20%3C%2FSTRONG%3E%20for%20each%20%3CSTRONG%3E0.05%3C%2FSTRONG%3E%20%3CSTRONG%3Eincrease.%3C%2FSTRONG%3E%20I%20can't%20figure%20out%20how%20to%20do%20this%20and%20I%20hope%20you%20can%20help%2C%20please%20look%20at%20the%20screenshot%20for%20more%20info%2C%20thanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1249801%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1249868%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20a%20field%20if%20another%20field%20is%20changed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1249868%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F579629%22%20target%3D%22_blank%22%3E%40flynn05%3C%2FA%3E%26nbsp%3BA%20mockup%20of%20the%20calculation%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1250240%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20a%20field%20if%20another%20field%20is%20changed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1250240%22%20slang%3D%22en-US%22%3E%3CP%3EHey%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F579629%22%20target%3D%22_blank%22%3E%40flynn05%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20first%20cell%20is%20one%20that%20you%20just%20input.%20So%20the%20exchange%20rate.%20Or%20you%20can%20use%20a%20drop%20down%20list.%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20second%20cell%20is%20a%20formula%20that%20does%20the%20math%20for%20you.%20It%20assumes%20a%20starting%20place.%20Looks%20like%20if%20exchange%20rate%20%3D%201%20then%20the%20projected%20sales%20is%20900.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3BI%20copied%20a%20picture%20of%20the%20formula%20that%20might%20be%20easier.%20But%20it%20basically%20starts%20with%20900%20and%20will%20do%20the%20calculation%20you%20asked%20for.%20It%20doent%20go%20up%2C%20so%20if%201%20is%20the%20highest%20and%20900%20sales%20is%20the%20highest%20then%20you%20are%20golden.%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20hard%20code%20the%20first%20part%20of%20the%20formula.%20So%20%3Dif(I3%20%3D%201%2C%20900%2C%3C%2FP%3E%3CP%3EI3%20is%20the%20reference%20to%20the%20cell%20that%20you%20will%20enter%20that%20is%20the%20exchange%20rate.%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20it%20does%20an%20if%20not%20statement.%20If%20its%20not%201%2C%20then%20take%201%20less%20the%20exchange%20rate%20and%20divide%20by%20.05.%26nbsp%3B%3C%2FP%3E%3CP%3EAnyhow.%20You%20should%20be%20good%20from%20here%20if%20you%20follow%20the%20picture%20and%20formula.%20Good%20luck.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1250268%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20a%20field%20if%20another%20field%20is%20changed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1250268%22%20slang%3D%22en-US%22%3EThanks%20so%20much%20for%20your%20help!%20It%E2%80%99s%20actually%20if%20the%20exchange%20rate%20%3D%200.7%20sales%20%3D%20900%20but%20I%E2%80%99ll%20just%20follow%20the%20picture%20and%20what%20you%20said%2C%20thanks!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1250286%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20a%20field%20if%20another%20field%20is%20changed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1250286%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F544433%22%20target%3D%22_blank%22%3E%40Savia%3C%2FA%3E%26nbsp%3BHi%20there%2C%20thanks%20for%20the%20response%20it%20works!%20I%20was%20also%20wondering%20if%20there%20is%20a%20way%20to%20make%20it%20for%20every%200.05%20drop%20sales%20increase%20by%2020.%20Thanks%20again!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1253229%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20a%20field%20if%20another%20field%20is%20changed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1253229%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F579629%22%20target%3D%22_blank%22%3E%40flynn05%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20should.%20Maybe%20change%20it%20to%20add%2020%20rather%20than%20-20.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

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

@flynn05 A mockup of the calculation attached.

Highlighted
Best Response confirmed by flynn05 (Occasional Contributor)
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. 

Highlighted
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

@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

@flynn05 

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