Conditional Custom Number Formatting

[Audiology]

Is there a non-VB conditional formula syntax to change the custom number formatting of a cell based on an external cell value. i.e.

if (A4 = 16) custom number format in cell C1 becomes #??/16

if (A4 = 8) custom number format in cell C1 becomes #??/8

if (A4 = 4) custom number format in cell C1 becomes #??/4


??

Thanks.

 

[Diablo II]

conditional formula can not do that, i dont think, but you can use "IF statments" to do that.

 

[Gerald Higgins]

Audiology - have you looked at the FRACTION format ? It's not one that I have ever used myself, but you might be able to make it work for you.

If you're struggling to do this, please post more details of what exactly you want to do. As Diablo II says, you might be able to use some kind of IF statement together with rounding to give the number of characters you want.

 

[Audiology]

Re: Conditional Custom Number Formatting: Solved

Tom, Gerald,

Thanks for the suggestions. In case anyone can use this, I figured it out as an IF statement:

In Cell C1, custom number-formatted as #??/??

=(IF(B1=16,(TEXT(A1/B1,("??/16"))),)&(IF(B1=8,(TEXT(A1/B1,("??/8"))),)&(IF(B1=4,(TEXT(A1/B1,("??/4"))),))))

results:

if A1 = 3 and B1 = 4 C1 = "3/4"
if A1 = 4 and B1 = 4 C1 = "4/4"
if A1 = 0 and B1 = 4 C1 = "0/4"
if A1 = 3 and B1 = 8 C1 = "3/8"
if A1 = 8 and B1 = 8 C1 = "8/8"
if A1 = 0 and B1 = 8 C1 = "0/8"
if A1 = 3 and B1 = 16 C1 = "3/16"
if A1 = 16 and B1 = 16 C1 = "16/16"
if A1 = 0 and B1 = 16 C1 = "0/16"

 

[Scott Huish]

You don't have to change the formatting for C1 because you are not returning a number, you are returning text strings.

I think this will do the same thing:

=IF(OR(B1={4,8,16}),A1&"/"&B1,"")

or with that exact formatting:

=IF(OR(B1={4,8,16}),TEXT(A1/B1,"??/"&B1),"")

 

[Audiology]

Hot...,

Your first statement is both effective and far more efficient than mine.

Thanks.