CODEWITHSUNDEEP
excelexcel-formulaexcel-charts
Michi
Michi

Reputation: 5471

Define decimal places of numbers based on user entry

I have the following Excel spreadsheet:

enter image description here

In Cells B7:D12 a report of the actual-budget-performance over the last 4 months is created based on the data base in Cells L7:N12.

To have a better presentation of the numbers the user can decide in Cell D2 if they should be shown for example in 1.000. In addition, the user can also decide in Cell D3 how many decimal places should be displayed.

To apply these user entries to the report I have the following formula in Cells C9:D12:

=FIXED((M9/$D$2),$D$3,FALSCH)

All this works fine.

Now, I want to create a chart in Cells G6:I14 based on the values Cells C9:D12 which does not work. The reason for this is probably that the FIXED function changes the numbers in Cells C9:D12 to text.

Therefore, I tried to solve the issue with this formula: =VALUE(FIXED((M9/$D$2),$D$3,FALSCH))

This formula changes the text back to a number but also eliminates the user entry in Cell D3 which defines the decimal places.

Is there a solution so the decimal places can be manually defined by the user and the chart can also be created?

I know I could create a seperate data base for the chart in which the values from Cells C9:D12 are converted back to numbers but I would prefer a solution without this workaround.

Upvotes: 0

Views: 38

Answers (1)

Samuel Hulla
Samuel Hulla

Reputation: 7089

Use the =ROUND(range, digits) formula in place of the FIXED() and it should work.

enter image description here

Upvotes: 1

Related Questions