Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to display the value in different format?
There is a number in cell A1, which is selected from a list
0.01, 0.1, 1, 10, 100, 1000 There is a given number 654.356 in cell B1 Does anyone have any suggestions on how to display the value in different format? For example, If the number in cell A1 is 0.01, then 654 should be displayed in cell C1. If the number in cell A1 is 0.1, then 654 should be displayed in cell C1. If the number in cell A1 is 1, then 654 should be displayed in cell C1. If the number in cell A1 is 10, then 654.3 should be displayed in cell C1. If the number in cell A1 is 100, then 654.36 should be displayed in cell C1. If the number in cell A1 is 1000, then 654.356 should be displayed in cell C1. Does anyone have any suggestions? Thanks in advance for any suggestions Eric |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to display the value in different format?
Eric,
In C1: =ROUND(B1,IF(A1<0,MAX(0,LOG(A1)),0)) I'm guessing that if A1 is 10, you really want 654.4 (?) HTH, Bernie MS Excel MVP "Eric" wrote in message ... There is a number in cell A1, which is selected from a list 0.01, 0.1, 1, 10, 100, 1000 There is a given number 654.356 in cell B1 Does anyone have any suggestions on how to display the value in different format? For example, If the number in cell A1 is 0.01, then 654 should be displayed in cell C1. If the number in cell A1 is 0.1, then 654 should be displayed in cell C1. If the number in cell A1 is 1, then 654 should be displayed in cell C1. If the number in cell A1 is 10, then 654.3 should be displayed in cell C1. If the number in cell A1 is 100, then 654.36 should be displayed in cell C1. If the number in cell A1 is 1000, then 654.356 should be displayed in cell C1. Does anyone have any suggestions? Thanks in advance for any suggestions Eric |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to display the value in different format?
Hello Eric,
=--TEXT(B1,"0."&REPT("0",INT(LOG(B1))-1+MAX(1,1+LOG(A1)))&"E+0") If you like to explore how I came up with this: http://www.sulprobil.com/html/nsig.html Regards, Bernd |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to display the value in different format?
Hello Bernie,
Of course your approach is shorter. One variant: =ROUND(B1,MAX(0,LOG(A1))) Regards, Bernd |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to display the value in different format?
Thank you very much for suggestions
I would like to change the display format by using Text function, rather than change its value. If I change it value without changing the display format, the cell would not display 654.4 in cell C1, if the cell format does not display any decimial 654. Do you have any suggestions? Thank you very much for any suggestions Eric "Bernie Deitrick" wrote: Eric, In C1: =ROUND(B1,IF(A1<0,MAX(0,LOG(A1)),0)) I'm guessing that if A1 is 10, you really want 654.4 (?) HTH, Bernie MS Excel MVP "Eric" wrote in message ... There is a number in cell A1, which is selected from a list 0.01, 0.1, 1, 10, 100, 1000 There is a given number 654.356 in cell B1 Does anyone have any suggestions on how to display the value in different format? For example, If the number in cell A1 is 0.01, then 654 should be displayed in cell C1. If the number in cell A1 is 0.1, then 654 should be displayed in cell C1. If the number in cell A1 is 1, then 654 should be displayed in cell C1. If the number in cell A1 is 10, then 654.3 should be displayed in cell C1. If the number in cell A1 is 100, then 654.36 should be displayed in cell C1. If the number in cell A1 is 1000, then 654.356 should be displayed in cell C1. Does anyone have any suggestions? Thanks in advance for any suggestions Eric |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to display the value in different format?
Eric,
The TEXT function also changes the value, just as the ROUND function does. The value in cell B1 is not changing - just the value as displayed in C1. So, I'm not sure what end result you want. Perhaps? =TEXT(B1,"0" & IF(A11,"."& REPT("0",LOG(A1)),"")) Bernie "Eric" wrote in message ... Thank you very much for suggestions I would like to change the display format by using Text function, rather than change its value. If I change it value without changing the display format, the cell would not display 654.4 in cell C1, if the cell format does not display any decimial 654. Do you have any suggestions? Thank you very much for any suggestions Eric "Bernie Deitrick" wrote: Eric, In C1: =ROUND(B1,IF(A1<0,MAX(0,LOG(A1)),0)) I'm guessing that if A1 is 10, you really want 654.4 (?) HTH, Bernie MS Excel MVP "Eric" wrote in message ... There is a number in cell A1, which is selected from a list 0.01, 0.1, 1, 10, 100, 1000 There is a given number 654.356 in cell B1 Does anyone have any suggestions on how to display the value in different format? For example, If the number in cell A1 is 0.01, then 654 should be displayed in cell C1. If the number in cell A1 is 0.1, then 654 should be displayed in cell C1. If the number in cell A1 is 1, then 654 should be displayed in cell C1. If the number in cell A1 is 10, then 654.3 should be displayed in cell C1. If the number in cell A1 is 100, then 654.36 should be displayed in cell C1. If the number in cell A1 is 1000, then 654.356 should be displayed in cell C1. Does anyone have any suggestions? Thanks in advance for any suggestions Eric |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to display the value in different format?
How does this relate to the question asked at 11:03 yesterday and answered
yesterday? "Does anyone have any suggestions on how to set the formula? There is a list of possible numbers in cell A1, such as 0.01, 0.1, 1, 10, 100, 1000 and there is a given number in cell B1, If 0.01 is inputed in cell A1 and 653.456 in cell B1, then return 653 in cell C1. If 0.1 is inputed in cell A1 and 653.456 in cell B1, then return 653 in cell C1. If 1 is inputed in cell A1 and 653.456 in cell B1, then return 653 in cell C1. If 10 is inputed in cell A1 and 653.456 in cell B1, then return 653.5 in cell C1. If 100 is inputed in cell A1 and 653.456 in cell B1, then return 653.46 in cell C1. If 1000 is inputed in cell A1 and 653.456 in cell B1, then return 653.456 in cell C1. Does anyone have any suggestions on how to set the formula in cell C1? Thanks in advance for any suggestions Eric " I don't understand where this series of almost identical questions is leading? -- David Biddulph "Eric" wrote in message ... There is a number in cell A1, which is selected from a list 0.01, 0.1, 1, 10, 100, 1000 There is a given number 654.356 in cell B1 Does anyone have any suggestions on how to display the value in different format? For example, If the number in cell A1 is 0.01, then 654 should be displayed in cell C1. If the number in cell A1 is 0.1, then 654 should be displayed in cell C1. If the number in cell A1 is 1, then 654 should be displayed in cell C1. If the number in cell A1 is 10, then 654.3 should be displayed in cell C1. If the number in cell A1 is 100, then 654.36 should be displayed in cell C1. If the number in cell A1 is 1000, then 654.356 should be displayed in cell C1. Does anyone have any suggestions? Thanks in advance for any suggestions Eric |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to display the value in different format?
When the question is solved by Round function, it works on the actual values,
but does not work on display format, if the cell is set to display no decimals at all, even through the actual value is 653.456, but it will display 653. By using Text function, it will solve my problem in one more step on showing the decimal without concerning the cell format. Thank everyone very much for suggestions Eric "David Biddulph" wrote: How does this relate to the question asked at 11:03 yesterday and answered yesterday? "Does anyone have any suggestions on how to set the formula? There is a list of possible numbers in cell A1, such as 0.01, 0.1, 1, 10, 100, 1000 and there is a given number in cell B1, If 0.01 is inputed in cell A1 and 653.456 in cell B1, then return 653 in cell C1. If 0.1 is inputed in cell A1 and 653.456 in cell B1, then return 653 in cell C1. If 1 is inputed in cell A1 and 653.456 in cell B1, then return 653 in cell C1. If 10 is inputed in cell A1 and 653.456 in cell B1, then return 653.5 in cell C1. If 100 is inputed in cell A1 and 653.456 in cell B1, then return 653.46 in cell C1. If 1000 is inputed in cell A1 and 653.456 in cell B1, then return 653.456 in cell C1. Does anyone have any suggestions on how to set the formula in cell C1? Thanks in advance for any suggestions Eric " I don't understand where this series of almost identical questions is leading? -- David Biddulph "Eric" wrote in message ... There is a number in cell A1, which is selected from a list 0.01, 0.1, 1, 10, 100, 1000 There is a given number 654.356 in cell B1 Does anyone have any suggestions on how to display the value in different format? For example, If the number in cell A1 is 0.01, then 654 should be displayed in cell C1. If the number in cell A1 is 0.1, then 654 should be displayed in cell C1. If the number in cell A1 is 1, then 654 should be displayed in cell C1. If the number in cell A1 is 10, then 654.3 should be displayed in cell C1. If the number in cell A1 is 100, then 654.36 should be displayed in cell C1. If the number in cell A1 is 1000, then 654.356 should be displayed in cell C1. Does anyone have any suggestions? Thanks in advance for any suggestions Eric |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to display the value in different format?
Hi,
An ever so slightly shorter, and maybe easier, formula would be: =TEXT(ROUND(B1,IF(A11,LOG(A1))),"General") Cheers, Shane Devenshire "Eric" wrote: There is a number in cell A1, which is selected from a list 0.01, 0.1, 1, 10, 100, 1000 There is a given number 654.356 in cell B1 Does anyone have any suggestions on how to display the value in different format? For example, If the number in cell A1 is 0.01, then 654 should be displayed in cell C1. If the number in cell A1 is 0.1, then 654 should be displayed in cell C1. If the number in cell A1 is 1, then 654 should be displayed in cell C1. If the number in cell A1 is 10, then 654.3 should be displayed in cell C1. If the number in cell A1 is 100, then 654.36 should be displayed in cell C1. If the number in cell A1 is 1000, then 654.356 should be displayed in cell C1. Does anyone have any suggestions? Thanks in advance for any suggestions Eric |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Display #'s incorrectly (format) | Excel Discussion (Misc queries) | |||
Display time format | Excel Discussion (Misc queries) | |||
How to display 5 into May in mmm format? | Excel Discussion (Misc queries) | |||
Cell format, want 100 to display as 1.00 | Excel Discussion (Misc queries) | |||
Display 0's in Accounting format | Excel Discussion (Misc queries) |