What-If Analysis ->Data Table - Formula/Mathematical formula used?

%3CLINGO-SUB%20id%3D%22lingo-sub-2021873%22%20slang%3D%22en-US%22%3EWhat-If%20Analysis%20-%26gt%3BData%20Table%20-%20Formula%2FMathematical%20formula%20used%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2021873%22%20slang%3D%22en-US%22%3E%3CP%3EHI%2C%3C%2FP%3E%3CP%3EI%20am%20working%20on%20one%20of%20the%20projects%20where%20I%20need%20to%20match%20the%20data%20manually%20genearted%20by%20Excel%20-%26gt%3BWhat%20If%20Analysis%20-%20Data%20Table.%3C%2FP%3E%3CP%3EI%20need%20the%20reference%20to%20how%20excel%20works%20in%20the%20back-end%20for%20the%20Data%20table%20function.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2021873%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-2022226%22%20slang%3D%22en-US%22%3ERe%3A%20What-If%20Analysis%20-%26gt%3BData%20Table%20-%20Formula%2FMathematical%20formula%20used%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2022226%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F914073%22%20target%3D%22_blank%22%3E%40Harjeetsaini10%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EExcel%20simply%20substitutes%20the%20values%20in%20the%20row%20above%20the%20data%20table%20and%2For%20the%20values%20in%20the%20column%20to%20the%20left%20of%20the%20data%20table%20into%20the%20formula%20in%20the%20upper%20left%20corner.%3C%2FP%3E%0A%3CP%3EIn%20the%20screenshot%20below%2C%20the%20formula%20is%20%3DA1%2BA2.%3C%2FP%3E%0A%3CP%3EA1%20is%20replaced%20with%20the%20values%20of%20B4%2C%20C4%2C%20...%3C%2FP%3E%0A%3CP%3EA2%20is%20replaced%20with%20the%20values%20of%20A5%2C%20A6%2C%20...%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESo%20for%20example%20B5%20is%20calculated%20as%20%3DB4%2BA5%2C%20and%20H10%20is%20calculated%20as%20%3DH4%2BA10%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0041.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F243470iD32F7655FEDB296E%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22S0041.png%22%20alt%3D%22S0041.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%E2%80%83%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

HI,

I am working on one of the projects where I need to match the data manually genearted by Excel ->What If Analysis - Data Table.

I need the reference to how excel works in the back-end for the Data table function.

1 Reply

@Harjeetsaini10 

 

Excel simply substitutes the values in the row above the data table and/or the values in the column to the left of the data table into the formula in the upper left corner.

In the screenshot below, the formula is =A1+A2.

A1 is replaced with the values of B4, C4, ...

A2 is replaced with the values of A5, A6, ...

 

So for example B5 is calculated as =B4+A5, and H10 is calculated as =H4+A10

 

S0041.png