SOLVED

I need a formula!

%3CLINGO-SUB%20id%3D%22lingo-sub-1967178%22%20slang%3D%22fr-FR%22%3EI%20need%20a%20formula!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1967178%22%20slang%3D%22fr-FR%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20calculate%20something%20but%20I%20have%20no%20idea%20what%20formula%20I%20could%20use.%20Let's%20say%20my%20forecasted%20expenses%20are%20of%20%24200%2C000%20and%20my%20current%20forecast%20is%20of%20%24204%2C000.%20I%20need%20a%20formula%20that's%20going%20to%20tell%20me%20in%20percentage%20if%20my%20current%20forecast%20is%20within%205%25%20of%20my%20forecasted%20expenditures%20(so%20%24190%2C000%20and%20%24210%2C000).%20In%20this%20case%2C%20the%20percentage%20should%20be%202%25%20but%20I%20have%20no%20idea%20what%20formula%20I%20could%20use%20to%20achieve%20that.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%20for%20the%20help!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEmilie%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1967178%22%20slang%3D%22fr-FR%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-1967208%22%20slang%3D%22en-US%22%3ERe%3A%20I%20need%20a%20formula!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1967208%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F894832%22%20target%3D%22_blank%22%3E%40Emilie21%3C%2FA%3E%26nbsp%3BPerhaps%20like%20in%20the%20picture%20below%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202020-12-08%20at%2015.52.27.png%22%20style%3D%22width%3A%20315px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F238657i9D0103F26B886FEC%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202020-12-08%20at%2015.52.27.png%22%20alt%3D%22Screenshot%202020-12-08%20at%2015.52.27.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EFALSE%20means%20not%20more%20than%202%25%20off%3C%2FP%3E%3CP%3ETRUE%20means%20more%20than%202%25%20off%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1967393%22%20slang%3D%22en-US%22%3ERe%3A%20I%20need%20a%20formula!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1967393%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3BI%20already%20have%20something%20similar%20that%20I'm%20using%20but%20I%20really%20need%20to%20show%20it%20in%20percentage%2C%20for%20example%2C%20for%20204%20000%24%20it%20has%20to%20show%202%25%20and%20for%20the%20205%20000%24%20it%20has%20to%20show%202%2C5%25.%20Also%2C%20the%20percentage%20doesn't%20need%20to%20stay%20in%20between%20-5%25%20and%205%25.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEmilie%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1967403%22%20slang%3D%22en-US%22%3ERe%3A%20I%20need%20a%20formula!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1967403%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F894832%22%20target%3D%22_blank%22%3E%40Emilie21%3C%2FA%3E%26nbsp%3BThat%20would%20be%26nbsp%3B%3DB1%2FA1-1%2C%20formatted%20as%20a%20percentage.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202020-12-08%20at%2017.16.48.png%22%20style%3D%22width%3A%20270px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F238671i91B9A24AA39E7612%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202020-12-08%20at%2017.16.48.png%22%20alt%3D%22Screenshot%202020-12-08%20at%2017.16.48.png%22%20%2F%3E%3C%2FSPAN%3E'%3C%2FP%3E%3CP%3EOr%20perhaps%20I%20don't%20follow.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi,

 

I need to calculate something but I have no idea what formula I could use. Let's say my forecasted expenditures are of 200 000$ and my current forecast is of 204 000$. I need a formula that's going to tell me in percentage if my current forecast is within 5% of my forecasted expenditures (so 190 000$ and 210 000$). In this case, the percentage should be 2% but I have no idea what formula I could use to achieve that.

 

Thanks in advance for the help! 

 

Emilie

5 Replies

@Emilie21 Perhaps like in the picture below:

Screenshot 2020-12-08 at 15.52.27.png

FALSE means not more than 2% off

TRUE means more than 2% off

@Riny_van_Eekelen I already have something similar that I'm using but I really need to show it in percentage, for example, for 204 000$ it has to show 2% and for the 205 000$ it has to show 2,5%. Also, the percentage doesn't need to stay in between -5% and 5%.

 

Emilie

best response confirmed by Emilie21 (New Contributor)
Solution

@Emilie21 That would be =B1/A1-1, formatted as a percentage.

Screenshot 2020-12-08 at 17.16.48.png'

Or perhaps I don't follow.

Thanks a lot! I hadn't seen it that way but it worked!

@Emilie21 Glad it worked out!