Price sensitivity Data table not calculating correctly

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

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


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


@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


See attached with both fixed.