SOLVED

What-if/Data table Problem

%3CLINGO-SUB%20id%3D%22lingo-sub-2105437%22%20slang%3D%22en-US%22%3EWhat-if%2FData%20table%20Problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2105437%22%20slang%3D%22en-US%22%3E%3CP%3EI%20tried%20to%20use%20the%20What%20if%20data%20table%20functionality%20contained%20in%20Excel.%3CBR%20%2F%3EDue%20to%20my%20skepticism%20about%20the%20functionality%2C%20I%20also%20calculated%20the%20results%20delivered%20in%20the%20traditional%20manner%20with%20equations.%3C%2FP%3E%3CP%3EUnfortunately%2C%20both%20approaches%20deliver%20different%20results.%3CBR%20%2F%3EI%20would%20be%20interested%20in%20where%20the%20deviations%20come%20from.%3CBR%20%2F%3EAs%20an%20example%2C%20I'll%20attach%20my%20EXCEL%20file.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EThe%20sample%20calculation%20comes%20from%20a%20special%20task%20of%20gear%20calculation%20with%20crossed%20axes.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20very%20grateful%20for%20any%20helpful%20answer.%3C%2FP%3E%3CP%3ER-Jensch%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2105437%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-2105537%22%20slang%3D%22en-US%22%3ERe%3A%20What-if%2FData%20table%20Problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2105537%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F945829%22%20target%3D%22_blank%22%3E%40R-Jensch%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI'd%20report%20it%20to%20Microsoft%20through%20File%20%26gt%3B%20Feedback%20%26gt%3B%20Send%20a%20Frown%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2105562%22%20slang%3D%22en-US%22%3ERe%3A%20What-if%2FData%20table%20Problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2105562%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20the%20meantime%20I%20have%20found%20out%20that%20for%20the%20calculation%20of%20the%20values%20in%20the%20data%20table%20it%20is%20crucial%20whether%20the%20values%20of%20the%20row%20input%20cells%20are%20calculated%20or%20not.%3CBR%20%2F%3EPlease%20look%20in%20my%26nbsp%3B%20extended%20example%20in%20the%20Excel%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I tried to use the What if data table functionality contained in Excel.
Due to my skepticism about the functionality, I also calculated the results delivered in the traditional manner with equations.

Unfortunately, both approaches deliver different results.
I would be interested in where the deviations come from.
As an example, I'll attach my EXCEL file.

The sample calculation comes from a special task of gear calculation with crossed axes.

 

I am very grateful for any helpful answer.

R-Jensch

3 Replies

@R-Jensch 

I'd report it to Microsoft through File > Feedback > Send a Frown

In the meantime I have found out that for the calculation of the values in the data table it is crucial whether the values of the row input cells are calculated or not.
Please look in my  extended example in the Excel file.

Best Response confirmed by R-Jensch (New Contributor)
Solution

@R-Jensch 

Ah yes. The input cells should not refer to the arguments of the original formula. If you replace the formula in G3 with its value, while keeping the formulas in H3:L3, the data table will return the correct values.