Formula to return a number with a certain amount of decimals

[RonOliver]

Hi!

2	9	9
		9.00
		9

So A1 is the number of decimals to be displayed. B1 is the number that I want displayed with decimals. Now, my problem is the ROUND function will get rid of the decimals if they are not necessary, the TEXT/CHOOSE functions will do what I want them to do but they will give me the number as a text (and thus, it is impossible for me to work with it further in other mathematical operations), and the VALUE/TEXT/CHOOSE functions will just do what the ROUND function did in the first place.

My question: is there a way for me to display the number in B1 with the amount of decimals specified in A1 even if decimals are not necessary? I want the amount of decimals to be dependent on A1, so if the value changes, the amount of decimals will change too. Can't use VBA, sorry.

Thank you very much!

 

[Tetra201]

... the TEXT/CHOOSE functions will do what I want them to do but they will give me the number as a text (and thus, it is impossible for me to work with it further in other mathematical operations)...

It is possible to use numbers-as-text in mathematical operations: e.g., ="9.00"+2 evaluates to 11. So, see if the following formula works for you:

=TEXT(B1,"0"&IF(A1>0,"."&REPT(0,A1),""))

 

[RonOliver]

Turns out that actually works. My mistake was also trying to use SUM or AVERAGE with texts (which still doesn't work for me as it is, but it does once I use the VALUE function when performing a mathematical operation). Thanks very much!

 

[ABC3132]

Turns out that actually works. My mistake was also trying to use SUM or AVERAGE with texts (which still doesn't work for me as it is, but it does once I use the VALUE function when performing a mathematical operation). Thanks very much!

Thanks RonOliver. It works. However it is possible to do the same without transforming them onto Text, keeping them as Numbers?. Thanks

 

[FormR]

it is possible to do the same without transforming them onto Text

As long as there's a sensible maximum for the number of decimal places you want to display, one option could be to use a series of conditional formatting rules.

Book1
	A	B	C
1	1	9	9.0
Sheet1

Cell Formulas
Range	Formula
C1	C1	=B1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1	Expression	=$A1=5	text	NO
C1	Expression	=$A1=4	text	NO
C1	Expression	=$A1=3	text	NO
C1	Expression	=$A1=2	text	NO
C1	Expression	=$A1=1	text	NO
C1	Expression	=$A1=0	text	NO

 

[ABC3132]

As long as there's a sensible maximum for the number of decimal places you want to display, one option could be to use a series of conditional formatting rules.

Book1
	A	B	C
1	1	9	9.0
Sheet1

Cell Formulas
Range	Formula
C1	C1	=B1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1	Expression	=$A1=5	text	NO
C1	Expression	=$A1=4	text	NO
C1	Expression	=$A1=3	text	NO
C1	Expression	=$A1=2	text	NO
C1	Expression	=$A1=1	text	NO
C1	Expression	=$A1=0	text	NO

Thanks FormR. I´m sorry but I havent´cach you

 

[FormR]

You can use conditional formatting rules to check the value of the cell that tells you how many decimal places you want to be displayed and have the condition format display that number.

You would need a rule to check for a 0 and display 0 decimal places, and another to check for a 1 and display 1, and another for 2 etc etc.

 

[FormR]

See if you can download an example file from here:

MrExcel_ post-6087057.xlsx

Shared with Dropbox

www.dropbox.com

Try changing the number in A1 to affect the dp's displayed in C1.

 

[ABC3132]

You can use conditional formatting rules to check the value of the cell that tells you how many decimal places you want to be displayed and have the condition format display that number.

You would need a rule to check for a 0 and display 0 decimal places, and another to check for a 1 and display 1, and another for 2 etc etc.

 

[FormR]

I think something has gone wrong with your reply in post#9 as I can't see anything other than the quote.