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

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

Hi CP!

You could use:

=IF(B1="U.S. Units",ROUND(E4,0), IF(B1="Metric Units",ROUND(G4,2)))
Format General

Note that the rounded figures are those actually stored in the formula
cell but that you do have the unrounded data in E4 and G4

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

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

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

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


.

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


.

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


.



.

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


.



.

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


.



.



.