# Conditional Custom Number Formatting

#### Audiology

##### Board Regular
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.

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### Diablo II

##### Well-known Member
conditional formula can not do that, i dont think, but you can use "IF statments" to do that.

#### Gerald Higgins

##### Well-known Member
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

##### Board Regular
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"

Last edited:

#### Scott Huish

##### MrExcel MVP
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),"")

Last edited:

#### Audiology

##### Board Regular
Hot...,

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

Thanks.

Replies
2
Views
75
Replies
0
Views
66
Replies
21
Views
669
Replies
10
Views
154
Replies
3
Views
121

1,191,696
Messages
5,988,149
Members
440,131
Latest member
EricMoz

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back