Price sensitivity Data table not calculating correctly

%3CLINGO-SUB%20id%3D%22lingo-sub-1546948%22%20slang%3D%22en-US%22%3ERe%3A%20Price%20sensitivity%20Data%20table%20not%20calculating%20correctly%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1546948%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F740438%22%20target%3D%22_blank%22%3E%40Orlin81%3C%2FA%3E%26nbsp%3B%2C%20it's%20very%20hard%20to%20visualize%20your%20issue.%20Can%20you%20share%20a%20sample%20workbook%20with%20confidential%20information%20removed%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1546965%22%20slang%3D%22en-US%22%3ERe%3A%20Price%20sensitivity%20Data%20table%20not%20calculating%20correctly%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1546965%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F383224%22%20target%3D%22_blank%22%3E%40TheAntony%3C%2FA%3E%26nbsp%3B%20here%20is%20a%20sample%20of%20what%20I%20am%20experiencing.%20any%20insight%20you%20can%20provide%20would%20be%20helpful%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1546887%22%20slang%3D%22en-US%22%3EPrice%20sensitivity%20Data%20table%20not%20calculating%20correctly%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1546887%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20conduct%20a%20price%20sensitivity%20analysis%20in%20excel%2C%20however%20when%20I%20input%20the%20row%20and%20collumn%20info%20the%20only%20result%20I%20get%20is%20the%20linked%20result%20I%20am%20trying%20to%20analyze.%20For%20instance%20if%20i%20put%20in%20%24100%20as%20my%20middle%20number%20and%20incrementcally%20increase%20by%2025%20up%20to%20200%20and%20then%20decrease%20to%20%2475%20for%20my%20collumn%20and%20in%20my%20row%20I%20put%20my%20middle%20number%20to%201000%20and%20increase%20by%20250%20up%20to%202000%20and%20then%20decrease%20to%20500.%20My%20profit%20that%20I%20link%20in%20to%20analyze%20is%20%2440%2C000%20in%20the%20upper%20left%20corner%20of%20the%20table.%20I%20click%20the%20what%20if%20button%20and%20data%20table.%20Input%20the%20number%20of%20Items%20which%20woulld%20be%201000%20for%20the%20row%20and%20in%20the%20collumn%20I%20clike%20the%20%24150%20and%20press%20ok.%20The%20result%20in%20each%20box%20returns%20%2440000.%20I%20have%20tried%20changing%20the%20formulas%20option%20from%20calculating%20everything%20except%20data%20tables%20to%20automatic%20and%20vice%20versa%20in%20the%20option%20but%20nothing%20resolves%20this.%20Here%20is%20the%20sample%20workbook.%20I%20see%20it%20doesn't%20have%20the%20table%20fillied%20our%20but%20fo%20rme%20it%20shows%2040000%20in%20every%20field.%20If%20I%20change%20that%20number%20it%20changes%20to%20match.%20I'm%20sure%20it%20is%20user%20error%20%3CLI-EMOJI%20id%3D%22lia_disappointed-face%22%20title%3D%22%3Adisappointed_face%3A%22%3E%3C%2FLI-EMOJI%3E%20Please%20help%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1546887%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1547029%22%20slang%3D%22en-US%22%3ERe%3A%20Price%20sensitivity%20Data%20table%20not%20calculating%20correctly%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1547029%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F740438%22%20target%3D%22_blank%22%3E%40Orlin81%3C%2FA%3E%26nbsp%3B%2C%20You%20had%202%20issues%20%3A%3C%2FP%3E%3COL%3E%3CLI%3EThere%20were%20no%20calculations%20just%20values%20in%20many%20of%20the%20items%20in%20the%20P%26amp%3BL%20section%3C%2FLI%3E%3CLI%3EThe%20calculations%20were%20set%20to%20Automatic%20except%20for%20data%20tables%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22TheAntony_0-1595823580584.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F208043iD6295F132330D2A8%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22TheAntony_0-1595823580584.png%22%20alt%3D%22TheAntony_0-1595823580584.png%22%20%2F%3E%3C%2FSPAN%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLI%3E%3C%2FOL%3E%3CP%3ESee%20attached%20with%20both%20fixed.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I am trying to conduct a price sensitivity analysis in excel, however when I input the row and collumn info the only result I get is the linked result I am trying to analyze. For instance if i put in $100 as my middle number and incrementcally increase by 25 up to 200 and then decrease to $75 for my collumn and in my row I put my middle number to 1000 and increase by 250 up to 2000 and then decrease to 500. My profit that I link in to analyze is $40,000 in the upper left corner of the table. I click the what if button and data table. Input the number of Items which woulld be 1000 for the row and in the collumn I clike the $150 and press ok. The result in each box returns $40000. I have tried changing the formulas option from calculating everything except data tables to automatic and vice versa in the option but nothing resolves this. Here is the sample workbook. I see it doesn't have the table fillied our but fo rme it shows 40000 in every field. If I change that number it changes to match. I'm sure it is user error Please help

 

3 Replies
Highlighted

@Orlin81 , it's very hard to visualize your issue. Can you share a sample workbook with confidential information removed?

Highlighted

@TheAntony  here is a sample of what I am experiencing. any insight you can provide would be helpful

Highlighted

@Orlin81 , You had 2 issues :

  1. There were no calculations just values in many of the items in the P&L section
  2. The calculations were set to Automatic except for data tables
    TheAntony_0-1595823580584.png

     

See attached with both fixed.