SOLVED

# ISSUE WITH WHAT IF ANALYSIS

Occasional Contributor

# ISSUE WITH WHAT IF ANALYSIS

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

8 Replies

# Re: ISSUE WITH WHAT IF ANALYSIS

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

# Re: ISSUE WITH WHAT IF ANALYSIS

I did, but it's still showing the same results.

# Re: ISSUE WITH WHAT IF ANALYSIS

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

How do you mean?

# Re: ISSUE WITH WHAT IF ANALYSIS

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.

# Re: ISSUE WITH WHAT IF ANALYSIS

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

# Re: ISSUE WITH WHAT IF ANALYSIS

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.

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.

# Re: ISSUE WITH WHAT IF ANALYSIS

That's great! Thanks for helping me.