2 differenct decimal options for same cell results

[cp]

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

 

[Max]

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"

 

[Norman Harker]

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

 

[Peo Sjoblom]

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)

 

[JulieD]

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]

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




.

 

[JulieD]

glad to assist

Thanks. This worked perfect!

cp

 

[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

 

[JulieD]

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]

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