If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
2 differenct decimal options for same cell results
I am trying to have the result of one cell show 2
decimals when the result is in liters and no decimals when the result is in fluid ounces. Is there a way to write the formula to automatically recognize which number format to use in this same cell? For example, in cell B1 I use the Data Validation formula "list" to select either "U.S. Units" or "Metric Units" from a source that contains the same text in 2 adjacent fields. In cell B4 below, I am using an If formula that says IF(B1="U.S. Units",E4, IF(B1="Metric Units",G4))..The number in E4 is 79 oz and the number in G4 is 2.34 liters. The problem is that the B4 cell either displays 79.12 oz, however I only want it to display 79 oz. IF I remove the decimals in the cell format feature then when I select Metric Units is shows 3 liters, and I want it to show 2.34. Suggestions? cp |
#2
|
|||
|
|||
2 differenct decimal options for same cell results
Try using ROUND() for E4, viz.:
= IF(B1="U.S. Units",ROUND(E4,0), IF(B1="Metric Units",G4)).. And format the cell as "General" -- Rgds Max xl 97 --- Please respond in thread xdemechanik atyahoodotcom ---- "cp" wrote in message ... I am trying to have the result of one cell show 2 decimals when the result is in liters and no decimals when the result is in fluid ounces. Is there a way to write the formula to automatically recognize which number format to use in this same cell? For example, in cell B1 I use the Data Validation formula "list" to select either "U.S. Units" or "Metric Units" from a source that contains the same text in 2 adjacent fields. In cell B4 below, I am using an If formula that says IF(B1="U.S. Units",E4, IF(B1="Metric Units",G4))..The number in E4 is 79 oz and the number in G4 is 2.34 liters. The problem is that the B4 cell either displays 79.12 oz, however I only want it to display 79 oz. IF I remove the decimals in the cell format feature then when I select Metric Units is shows 3 liters, and I want it to show 2.34. Suggestions? cp |
#3
|
|||
|
|||
2 differenct decimal options for same cell results
|
#4
|
|||
|
|||
2 differenct decimal options for same cell results
You could use something like
IF(B1="U.S. Units",FLOOR(E4,1),IF(B1=Metric Units,G4,etc)) FLOOR rounds down, if you want to round to nearest integer use =ROUND(E4,0) -- Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in the newsgroup/forum) "cp" wrote in message ... I am trying to have the result of one cell show 2 decimals when the result is in liters and no decimals when the result is in fluid ounces. Is there a way to write the formula to automatically recognize which number format to use in this same cell? For example, in cell B1 I use the Data Validation formula "list" to select either "U.S. Units" or "Metric Units" from a source that contains the same text in 2 adjacent fields. In cell B4 below, I am using an If formula that says IF(B1="U.S. Units",E4, IF(B1="Metric Units",G4))..The number in E4 is 79 oz and the number in G4 is 2.34 liters. The problem is that the B4 cell either displays 79.12 oz, however I only want it to display 79 oz. IF I remove the decimals in the cell format feature then when I select Metric Units is shows 3 liters, and I want it to show 2.34. Suggestions? cp |
#5
|
|||
|
|||
2 differenct decimal options for same cell results
Hi cp
have you tried IF(B1="U.S. Units",Round(E4,0), Round(G4,2)) do you need the additional if? this, of course, actually rounds the numbers to the specified number of decimals not just hides the decimal places Cheers JulieD "cp" wrote in message ... I am trying to have the result of one cell show 2 decimals when the result is in liters and no decimals when the result is in fluid ounces. Is there a way to write the formula to automatically recognize which number format to use in this same cell? For example, in cell B1 I use the Data Validation formula "list" to select either "U.S. Units" or "Metric Units" from a source that contains the same text in 2 adjacent fields. In cell B4 below, I am using an If formula that says IF(B1="U.S. Units",E4, IF(B1="Metric Units",G4))..The number in E4 is 79 oz and the number in G4 is 2.34 liters. The problem is that the B4 cell either displays 79.12 oz, however I only want it to display 79 oz. IF I remove the decimals in the cell format feature then when I select Metric Units is shows 3 liters, and I want it to show 2.34. Suggestions? cp |
#6
|
|||
|
|||
2 differenct decimal options for same cell results
Thanks. This worked perfect!
cp -----Original Message----- Hi cp have you tried IF(B1="U.S. Units",Round(E4,0), Round(G4,2)) do you need the additional if? this, of course, actually rounds the numbers to the specified number of decimals not just hides the decimal places Cheers JulieD "cp" wrote in message ... I am trying to have the result of one cell show 2 decimals when the result is in liters and no decimals when the result is in fluid ounces. Is there a way to write the formula to automatically recognize which number format to use in this same cell? For example, in cell B1 I use the Data Validation formula "list" to select either "U.S. Units" or "Metric Units" from a source that contains the same text in 2 adjacent fields. In cell B4 below, I am using an If formula that says IF(B1="U.S. Units",E4, IF(B1="Metric Units",G4))..The number in E4 is 79 oz and the number in G4 is 2.34 liters. The problem is that the B4 cell either displays 79.12 oz, however I only want it to display 79 oz. IF I remove the decimals in the cell format feature then when I select Metric Units is shows 3 liters, and I want it to show 2.34. Suggestions? cp . |
#7
|
|||
|
|||
2 differenct decimal options for same cell results
glad to assist
"cp" wrote in message ... Thanks. This worked perfect! cp -----Original Message----- Hi cp have you tried IF(B1="U.S. Units",Round(E4,0), Round(G4,2)) do you need the additional if? this, of course, actually rounds the numbers to the specified number of decimals not just hides the decimal places Cheers JulieD "cp" wrote in message ... I am trying to have the result of one cell show 2 decimals when the result is in liters and no decimals when the result is in fluid ounces. Is there a way to write the formula to automatically recognize which number format to use in this same cell? For example, in cell B1 I use the Data Validation formula "list" to select either "U.S. Units" or "Metric Units" from a source that contains the same text in 2 adjacent fields. In cell B4 below, I am using an If formula that says IF(B1="U.S. Units",E4, IF(B1="Metric Units",G4))..The number in E4 is 79 oz and the number in G4 is 2.34 liters. The problem is that the B4 cell either displays 79.12 oz, however I only want it to display 79 oz. IF I remove the decimals in the cell format feature then when I select Metric Units is shows 3 liters, and I want it to show 2.34. Suggestions? cp . |
#8
|
|||
|
|||
2 differenct decimal options for same cell results
Julie,
I did have one more question. Perhaps I should submit this as a new post. In any event. Do you know if there is a way to have a cell act as an input field as well as a calcuation field? In other words, if I input the number 170 in cell A1, and in cell B1 I choose "US Ounces" from the drop down list, then cell E4 displays 79 ounces, which is correct. The question is, is there a way to change the data in cell A1 if I choose "Metric" from the drop down list so it calcuates in kilograms instead of pounds? I have not heard of a way to change an input cell, but have seen forms on the internet that appear to do this. cp -----Original Message----- glad to assist "cp" wrote in message ... Thanks. This worked perfect! cp -----Original Message----- Hi cp have you tried IF(B1="U.S. Units",Round(E4,0), Round(G4,2)) do you need the additional if? this, of course, actually rounds the numbers to the specified number of decimals not just hides the decimal places Cheers JulieD "cp" wrote in message ... I am trying to have the result of one cell show 2 decimals when the result is in liters and no decimals when the result is in fluid ounces. Is there a way to write the formula to automatically recognize which number format to use in this same cell? For example, in cell B1 I use the Data Validation formula "list" to select either "U.S. Units" or "Metric Units" from a source that contains the same text in 2 adjacent fields. In cell B4 below, I am using an If formula that says IF(B1="U.S. Units",E4, IF (B1="Metric Units",G4))..The number in E4 is 79 oz and the number in G4 is 2.34 liters. The problem is that the B4 cell either displays 79.12 oz, however I only want it to display 79 oz. IF I remove the decimals in the cell format feature then when I select Metric Units is shows 3 liters, and I want it to show 2.34. Suggestions? cp . . |
#9
|
|||
|
|||
2 differenct decimal options for same cell results
Hi cp
you can only (AFAIK) do this via code but i'm stuck on what you want to change A1 to the way i see what you want to do is scenario 1 A1 = 170 B1 = US Ounces E4 = 79oz scenario 2 A1 = 170 B1 = Metric A1 = ? if you'ld like to post what you want to see & the calculation necessary i'll see if i can scrape together some code for you. Cheers JulieD "cp" wrote in message ... Julie, I did have one more question. Perhaps I should submit this as a new post. In any event. Do you know if there is a way to have a cell act as an input field as well as a calcuation field? In other words, if I input the number 170 in cell A1, and in cell B1 I choose "US Ounces" from the drop down list, then cell E4 displays 79 ounces, which is correct. The question is, is there a way to change the data in cell A1 if I choose "Metric" from the drop down list so it calcuates in kilograms instead of pounds? I have not heard of a way to change an input cell, but have seen forms on the internet that appear to do this. cp -----Original Message----- glad to assist "cp" wrote in message ... Thanks. This worked perfect! cp -----Original Message----- Hi cp have you tried IF(B1="U.S. Units",Round(E4,0), Round(G4,2)) do you need the additional if? this, of course, actually rounds the numbers to the specified number of decimals not just hides the decimal places Cheers JulieD "cp" wrote in message ... I am trying to have the result of one cell show 2 decimals when the result is in liters and no decimals when the result is in fluid ounces. Is there a way to write the formula to automatically recognize which number format to use in this same cell? For example, in cell B1 I use the Data Validation formula "list" to select either "U.S. Units" or "Metric Units" from a source that contains the same text in 2 adjacent fields. In cell B4 below, I am using an If formula that says IF(B1="U.S. Units",E4, IF (B1="Metric Units",G4))..The number in E4 is 79 oz and the number in G4 is 2.34 liters. The problem is that the B4 cell either displays 79.12 oz, however I only want it to display 79 oz. IF I remove the decimals in the cell format feature then when I select Metric Units is shows 3 liters, and I want it to show 2.34. Suggestions? cp . . |
#10
|
|||
|
|||
2 differenct decimal options for same cell results
Thanks Julie. Acutally the scenario I am looking to
create is more like: scenario 1 A1={Select} (part of my Data Validation List) A2=U.S. Units (part of my Data Validation List) A3=Metric Units (part of my Data Validation List) B1= U.S Units: (User selects from Data Validation list) B2= 170: (Where the user typed in 170) B4 = 57 oz: =IF(B1="{Select}","0",IF(B1="U.S. Units",ROUND(E4,0), IF(B1="Metric Units",ROUND(G4,2)))) Note: There is another calculation I use to get to 79 ounces but let's leave that out for now to simplify things. E2=170: =IF(B1="{Select}",B2,IF(B1="U.S. Units",B2, IF (B1="Metric Units",B2/0.4536))) E4 =57 oz: =E2*10/29.5741 G2=77kg: =IF(B1="{Select}",B2*0.4536,IF(B1="U.S. Units",B2*0.4536, IF(B1="Metric Units",B2)))) G4=1.70 liters: =2/0.4536*10/1000 scenario 2 Ideally, I would like to be able to select "Metric" from the drop down Data Validation list in B1 and have the data that was originally input (typed in 170) in B2 to change to 77 kg. I can't picture how a field that is an "input" field could also have a formula associated with it. cp -----Original Message----- Hi cp you can only (AFAIK) do this via code but i'm stuck on what you want to change A1 to the way i see what you want to do is scenario 1 A1 = 170 B1 = US Ounces E4 = 79oz scenario 2 A1 = 170 B1 = Metric A1 = ? if you'ld like to post what you want to see & the calculation necessary i'll see if i can scrape together some code for you. Cheers JulieD "cp" wrote in message ... Julie, I did have one more question. Perhaps I should submit this as a new post. In any event. Do you know if there is a way to have a cell act as an input field as well as a calcuation field? In other words, if I input the number 170 in cell A1, and in cell B1 I choose "US Ounces" from the drop down list, then cell E4 displays 79 ounces, which is correct. The question is, is there a way to change the data in cell A1 if I choose "Metric" from the drop down list so it calcuates in kilograms instead of pounds? I have not heard of a way to change an input cell, but have seen forms on the internet that appear to do this. cp -----Original Message----- glad to assist "cp" wrote in message ... Thanks. This worked perfect! cp -----Original Message----- Hi cp have you tried IF(B1="U.S. Units",Round(E4,0), Round(G4,2)) do you need the additional if? this, of course, actually rounds the numbers to the specified number of decimals not just hides the decimal places Cheers JulieD "cp" wrote in message ... I am trying to have the result of one cell show 2 decimals when the result is in liters and no decimals when the result is in fluid ounces. Is there a way to write the formula to automatically recognize which number format to use in this same cell? For example, in cell B1 I use the Data Validation formula "list" to select either "U.S. Units" or "Metric Units" from a source that contains the same text in 2 adjacent fields. In cell B4 below, I am using an If formula that says IF(B1="U.S. Units",E4, IF (B1="Metric Units",G4))..The number in E4 is 79 oz and the number in G4 is 2.34 liters. The problem is that the B4 cell either displays 79.12 oz, however I only want it to display 79 oz. IF I remove the decimals in the cell format feature then when I select Metric Units is shows 3 liters, and I want it to show 2.34. Suggestions? cp . . . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Cloning a Cell | theDutchie | Tables | 2 | May 17th, 2004 01:16 PM |
How to create drop down list having options to the right of cell | Vipul Vashisht | Worksheet Functions | 5 | November 15th, 2003 02:12 PM |
Cell reference data not displaying in chart | Jon Peltier | Charts and Charting | 0 | November 12th, 2003 06:30 PM |
Multiple SUMIF Statements | Gary Thomson | Worksheet Functions | 4 | October 28th, 2003 11:16 AM |
Convert a Cell Reference to Text | Chuck Buker | Worksheet Functions | 6 | September 22nd, 2003 05:04 PM |