Changing decimal numbers in protected sheet

[Mette Nygaard]

Hi

At our lab we use protected worksheets to calculate analytical results ranging from 0,00001 to 1000000.

In order to allow the users to change the number of digits, that they want for a given set of results, a cell - e.g. A1 - is left unprotected for the entry of the desired number of digits:

A user may enter a number in this cell - e.g. A1=3 - and the results calculated in the worksheet is then rounded to 3 digits by means of the worksheet function "Round(Result;A1)". However - zero is not shown: e.g. 1,40089 is rounded to 1,401, but 1,40000 is rounded to 1,4 and not 1,400.

Do any of you know wich function to use, if we want Excel to return a given result with a number of digits specified by our users - including "0"?

Kind regards, Mette

 

[mike31z]

Is this what you mean? if you change the numer in A1 the number of decimals change, if you enter 0 you get whole number displayed. This takes 2 columns 1 is your source the other is your rounding.

Sorry this is the wrong side of the decimal

Mike

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Tahoma,Arial; font-size:12pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:80px;" /><col style="width:132px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >*</td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:right; ">4</td><td style="text-align:right; ">22.33</td><td >*</td><td style="text-align:right; ">22.33</td><td >*</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >*</td><td style="text-align:right; ">22.36587</td><td >*</td><td style="text-align:right; ">22.3659</td><td >*</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >*</td><td style="text-align:right; ">21.569871</td><td >*</td><td style="text-align:right; ">21.5699</td><td >*</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >*</td><td style="text-align:right; ">12.36985215</td><td >*</td><td style="text-align:right; ">12.3699</td><td >*</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >*</td><td >*</td><td >*</td><td >*</td><td >*</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >D1</td><td >=ROUND(B1,$A$1)</td></tr><tr><td >D2</td><td >=ROUND(B2,$A$1)</td></tr><tr><td >D3</td><td >=ROUND(B3,$A$1)</td></tr><tr><td >D4</td><td >=ROUND(B4,$A$1)</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

 

[lenze]

Do you mean rounded to that number of Decimals?? What cell(s) does this apply top?
What's the MAX number of decimals?

lenze

 

[mike31z]

After third reading of the original post. I think they want to round the whole number so they should be dividing the source cell by 100

The placement of the comma is the problem for me.

mike

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Tahoma,Arial; font-size:12pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:80px;" /><col style="width:132px;" /><col style="width:80px;" /><col style="width:154px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >*</td><td >A</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">100</td><td style="text-align:right; ">140089</td><td >*</td><td style="text-align:right; "> * * * * * * * * * *1,401 </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >*</td><td style="text-align:right; ">2345678</td><td >*</td><td style="text-align:right; "> * * * * * * * * *23,457 </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >*</td><td style="text-align:right; ">1000000</td><td >*</td><td style="text-align:right; "> * * * * * * * * *10,000 </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td >*</td><td style="text-align:right; ">9876543</td><td >*</td><td style="text-align:right; "> * * * * * * * * *98,765 </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >D8</td><td >=B8/$A$8</td></tr><tr><td >D9</td><td >=B9/$A$8</td></tr><tr><td >D10</td><td >=B10/$A$8</td></tr><tr><td >D11</td><td >=B11/$A$8</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

 

[Mette Nygaard]

Thank you for your answers. Sorry if my question is a bit unclear. Let me try to illustrate my problem:

What I have today in Excel is this:

<TABLE style="WIDTH: 350pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=466 border=0 x:str><COLGROUP><COL style="WIDTH: 27pt; mso-width-source: userset; mso-width-alt: 1316" width=36><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2523" width=69><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2377" width=65><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2157" width=59><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" width=81><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2450" width=67><COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3254" width=89><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl38 style="BORDER-RIGHT: #333333 0.5pt solid; BORDER-TOP: #333333 0.5pt solid; BORDER-LEFT: #333333 0.5pt solid; WIDTH: 27pt; BORDER-BOTTOM: #333333 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=36 height=17> </TD><TD class=xl38 style="BORDER-RIGHT: #333333 0.5pt solid; BORDER-TOP: #333333 0.5pt solid; BORDER-LEFT: #333333; WIDTH: 52pt; BORDER-BOTTOM: #333333 0.5pt solid; BACKGROUND-COLOR: transparent" width=69>A</TD><TD class=xl38 style="BORDER-RIGHT: #333333 0.5pt solid; BORDER-TOP: #333333 0.5pt solid; BORDER-LEFT: #333333; WIDTH: 49pt; BORDER-BOTTOM: #333333 0.5pt solid; BACKGROUND-COLOR: transparent" width=65>B</TD><TD class=xl38 style="BORDER-RIGHT: #333333 0.5pt solid; BORDER-TOP: #333333 0.5pt solid; BORDER-LEFT: #333333; WIDTH: 44pt; BORDER-BOTTOM: #333333 0.5pt solid; BACKGROUND-COLOR: transparent" width=59>C</TD><TD class=xl38 style="BORDER-RIGHT: #333333 0.5pt solid; BORDER-TOP: #333333 0.5pt solid; BORDER-LEFT: #333333; WIDTH: 61pt; BORDER-BOTTOM: #333333 0.5pt solid; BACKGROUND-COLOR: transparent" width=81>D</TD><TD class=xl38 style="BORDER-RIGHT: #333333 0.5pt solid; BORDER-TOP: #333333 0.5pt solid; BORDER-LEFT: #333333; WIDTH: 50pt; BORDER-BOTTOM: #333333 0.5pt solid; BACKGROUND-COLOR: transparent" width=67>E</TD><TD class=xl38 style="BORDER-RIGHT: #333333 0.5pt solid; BORDER-TOP: #333333 0.5pt solid; BORDER-LEFT: #333333; WIDTH: 67pt; BORDER-BOTTOM: #333333 0.5pt solid; BACKGROUND-COLOR: transparent" width=89>F</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl38 style="BORDER-RIGHT: #333333 0.5pt solid; BORDER-TOP: #333333; BORDER-LEFT: #333333 0.5pt solid; BORDER-BOTTOM: #333333 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>1</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=4>Number of decimal places for density</TD><TD class=xl33 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>4</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl38 style="BORDER-RIGHT: #333333 0.5pt solid; BORDER-TOP: #333333; BORDER-LEFT: #333333 0.5pt solid; BORDER-BOTTOM: #333333 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>2</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl31 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl38 style="BORDER-RIGHT: #333333 0.5pt solid; BORDER-TOP: #333333; BORDER-LEFT: #333333 0.5pt solid; BORDER-BOTTOM: #333333 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>3</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl31 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl38 style="BORDER-RIGHT: #333333 0.5pt solid; BORDER-TOP: #333333; BORDER-LEFT: #333333 0.5pt solid; BORDER-BOTTOM: #333333 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>4</TD><TD class=xl35 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"> </TD><TD class=xl27 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Weight</TD><TD class=xl27 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Volume</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Density</TD><TD class=xl31 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=2>Function used for</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl38 style="BORDER-RIGHT: #333333 0.5pt solid; BORDER-TOP: #333333; BORDER-LEFT: #333333 0.5pt solid; BORDER-BOTTOM: #333333 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>5</TD><TD class=xl36 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl29 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">(g)</TD><TD class=xl29 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">(ml)</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">(g/ml)</TD><TD class=xl31 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=2>calculating density:</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl38 style="BORDER-RIGHT: #333333 0.5pt solid; BORDER-TOP: #333333; BORDER-LEFT: #333333 0.5pt solid; BORDER-BOTTOM: #333333 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>6</TD><TD class=xl35 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Sample 1</TD><TD class=xl32 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num="1.2625">1,2625</TD><TD class=xl32 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>1</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num="1.2625" x:fmla="=ROUND(C7/D7,$F$2)">1,2625</TD><TD class=xl31 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=2> =round(B6/C6;$E$1)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl38 style="BORDER-RIGHT: #333333 0.5pt solid; BORDER-TOP: #333333; BORDER-LEFT: #333333 0.5pt solid; BORDER-BOTTOM: #333333 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>7</TD><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Sample 2</TD><TD class=xl33 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num="1.2625">1,2625</TD><TD class=xl33 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num="1.2625">1,2625</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num x:fmla="=ROUND(C8/D8,$F$2)">1</TD><TD class=xl31 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=2> =round(B7/C7;$E$1)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl38 style="BORDER-RIGHT: #333333 0.5pt solid; BORDER-TOP: #333333; BORDER-LEFT: #333333 0.5pt solid; BORDER-BOTTOM: #333333 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>8</TD><TD class=xl36 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Sample 3</TD><TD class=xl34 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="10.3">10,3</TD><TD class=xl34 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>10</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="1.03" x:fmla="=ROUND(C9/D9,$F$2)">1,03</TD><TD class=xl31 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=2> =round(B8/C8;$E$1)</TD></TR></TBODY></TABLE>

Cells with blue text are formatted as unlocked cells (for user entries).
The rest of the cells are locked, no entries or formatting is allowed.
Problem: Results are requested with 4 deicmal places, but the "round" function doesn't show decimal places containing "0".


What I would like to have in Excel is this:

<TABLE style="WIDTH: 350pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=466 border=0 x:str><COLGROUP><COL style="WIDTH: 27pt; mso-width-source: userset; mso-width-alt: 1316" width=36><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2523" width=69><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2377" width=65><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2157" width=59><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" width=81><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2450" width=67><COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3254" width=89><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl35 style="BORDER-RIGHT: #333333 0.5pt solid; BORDER-TOP: #333333 0.5pt solid; BORDER-LEFT: #333333 0.5pt solid; WIDTH: 27pt; BORDER-BOTTOM: #333333 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=36 height=17> </TD><TD class=xl35 style="BORDER-RIGHT: #333333 0.5pt solid; BORDER-TOP: #333333 0.5pt solid; BORDER-LEFT: #333333; WIDTH: 52pt; BORDER-BOTTOM: #333333 0.5pt solid; BACKGROUND-COLOR: transparent" width=69>A</TD><TD class=xl35 style="BORDER-RIGHT: #333333 0.5pt solid; BORDER-TOP: #333333 0.5pt solid; BORDER-LEFT: #333333; WIDTH: 49pt; BORDER-BOTTOM: #333333 0.5pt solid; BACKGROUND-COLOR: transparent" width=65>B</TD><TD class=xl35 style="BORDER-RIGHT: #333333 0.5pt solid; BORDER-TOP: #333333 0.5pt solid; BORDER-LEFT: #333333; WIDTH: 44pt; BORDER-BOTTOM: #333333 0.5pt solid; BACKGROUND-COLOR: transparent" width=59>C</TD><TD class=xl35 style="BORDER-RIGHT: #333333 0.5pt solid; BORDER-TOP: #333333 0.5pt solid; BORDER-LEFT: #333333; WIDTH: 61pt; BORDER-BOTTOM: #333333 0.5pt solid; BACKGROUND-COLOR: transparent" width=81>D</TD><TD class=xl35 style="BORDER-RIGHT: #333333 0.5pt solid; BORDER-TOP: #333333 0.5pt solid; BORDER-LEFT: #333333; WIDTH: 50pt; BORDER-BOTTOM: #333333 0.5pt solid; BACKGROUND-COLOR: transparent" width=67>E</TD><TD class=xl35 style="BORDER-RIGHT: #333333 0.5pt solid; BORDER-TOP: #333333 0.5pt solid; BORDER-LEFT: #333333; WIDTH: 67pt; BORDER-BOTTOM: #333333 0.5pt solid; BACKGROUND-COLOR: transparent" width=89>F</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl35 style="BORDER-RIGHT: #333333 0.5pt solid; BORDER-TOP: #333333; BORDER-LEFT: #333333 0.5pt solid; BORDER-BOTTOM: #333333 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>1</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=4>Number of decimal places for density</TD><TD class=xl30 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>4</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl35 style="BORDER-RIGHT: #333333 0.5pt solid; BORDER-TOP: #333333; BORDER-LEFT: #333333 0.5pt solid; BORDER-BOTTOM: #333333 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>2</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl28 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl35 style="BORDER-RIGHT: #333333 0.5pt solid; BORDER-TOP: #333333; BORDER-LEFT: #333333 0.5pt solid; BORDER-BOTTOM: #333333 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>3</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl28 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl35 style="BORDER-RIGHT: #333333 0.5pt solid; BORDER-TOP: #333333; BORDER-LEFT: #333333 0.5pt solid; BORDER-BOTTOM: #333333 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>4</TD><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"> </TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Weight</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Volume</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Density</TD><TD class=xl28 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=2>Function used for</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl35 style="BORDER-RIGHT: #333333 0.5pt solid; BORDER-TOP: #333333; BORDER-LEFT: #333333 0.5pt solid; BORDER-BOTTOM: #333333 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>5</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">(g)</TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">(ml)</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">(g/ml)</TD><TD class=xl28 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=2>calculating density:</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl35 style="BORDER-RIGHT: #333333 0.5pt solid; BORDER-TOP: #333333; BORDER-LEFT: #333333 0.5pt solid; BORDER-BOTTOM: #333333 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>6</TD><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Sample 1</TD><TD class=xl29 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num="1.2625">1,2625</TD><TD class=xl29 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>1</TD><TD class=xl36 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num="1.2625" x:fmla="=ROUND(C7/D7,$F$2)">1,2625</TD><TD class=xl28 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">?</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl35 style="BORDER-RIGHT: #333333 0.5pt solid; BORDER-TOP: #333333; BORDER-LEFT: #333333 0.5pt solid; BORDER-BOTTOM: #333333 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>7</TD><TD class=xl34 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Sample 2</TD><TD class=xl30 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num="1.2625">1,2625</TD><TD class=xl30 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num="1.2625">1,2625</TD><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num="1" x:fmla="=ROUND(C8/D8,$F$2)">1,0000</TD><TD class=xl28 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">?</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl35 style="BORDER-RIGHT: #333333 0.5pt solid; BORDER-TOP: #333333; BORDER-LEFT: #333333 0.5pt solid; BORDER-BOTTOM: #333333 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>8</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Sample 3</TD><TD class=xl31 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="10.3">10,3</TD><TD class=xl31 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>10</TD><TD class=xl38 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="1.03" x:fmla="=ROUND(C9/D9,$F$2)">1,0300</TD><TD class=xl28 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">?</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR></TBODY></TABLE>

Here the user have requested 4 decimal places for the results by entering "4" in cell E4, and all Density results are shown with 4 decimal places.
How do I get Excel to do this?

(Have tried to look under Conditional formatting, but no formatting of numbers is possible here ...)

Mette

 

[Andrew Poulsom]

Prior to Excel 2007 what you want isn't possible without a macro. Do you want a macro solution?

 

[mike31z]

Have you tried putting a 0 in your cell E1.

In the example of mine I entered a 0 in cell A1 and received a whole number in D1:D4

Mike

 

[mike31z]

Disregard previous post.
I see the problem, not the solution.

in some cases you need to add the trailing zeros and excel won't add that unless you format the column/cell to return a specific number of decimal places.

 

[mikerickson]

From the OP, it sounds that in addition to changing the second argument of ROUND, the desire is to have the number format also set to "#." & REPT("0",A1)

i.e. if A1 =3 then the number format is #.000
A1 = 4, number format #.0000

This could be mimicked with =TEXT(ROUND(result,A1), "#." & REPT("0",A1) )
but that would return a string and =TEXT(ROUND(result,A1), "#." & REPT("0",A1) )+0 would just coerce it back to general format.

My guess is that VB event code would be needed.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$A$1" Then
    On Error Resume Next
    ActiveSheet.Unprotect
    Cells.NumberFormat = "#." & String(Val(CStr(Target.Value)), "0")
    ActiveSheet.Protect
    On Error GoTo 0
End If
End Sub