SOLVED

ISSUE WITH WHAT IF ANALYSIS

Copper Contributor

I am trying to create a data table with two variables, but when I'm initiating the what-if analysis, the data2021-07-20.png are coming all wrong. 

8 Replies

@rahuldavidmondal 

 

Your screenshot shows a one-dimensional data table. You should specify E5 (the amount) as Column Input cell:

 

S0602.png

@Hans Vogelaar 

I did, but it's still showing the same results. 2021-07-21 (1).png

@rahuldavidmondal 

Are you sure that E6 contains the formula =E4*E5 and that F8 contains the formula =E6 ?

How do you mean?

@rahuldavidmondal 

Select cell E6. What do you see in the formula bar?

Then select cell F8. What do you see in the formula bar?

 

If you wish, you can attach your sample workbook to a reply. That will enable us to investigate it directly.

@Hans Vogelaar 

When I am selecting the specified cells, it's showing the value and not any formulas. 

I am attaching the file, please check. 

Thank You.

 

best response confirmed by allyreckerman (Microsoft)
Solution

@rahuldavidmondal 

1) All your currency values are text values, not numbers. You should remove the Rs. from the cells, and set the number format to a currency format that uses Rs.

'.png

2) A data table will only work if you have formulas. Without formulas, Excel has no way of knowing what you want to calculate.

In E6, enter the formula =E4*E5. This causes Excel to calculate the total price based on the price per apple and the quantity.

In F8, enter the formula =E6. This makes the top right cell of the data table equal to the total price in E6.

 

See the attached version.

 

 

That's great! Thanks for helping me.
1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
Solution

@rahuldavidmondal 

1) All your currency values are text values, not numbers. You should remove the Rs. from the cells, and set the number format to a currency format that uses Rs.

'.png

2) A data table will only work if you have formulas. Without formulas, Excel has no way of knowing what you want to calculate.

In E6, enter the formula =E4*E5. This causes Excel to calculate the total price based on the price per apple and the quantity.

In F8, enter the formula =E6. This makes the top right cell of the data table equal to the total price in E6.

 

See the attached version.

 

 

View solution in original post