# Display number as fraction 1/x

#### Formula11

##### Board Regular
How do I display a number as a fraction of "1/something".
At the same time I need to use the cell value in another calulation, so it needs to be formatted as some type of number (not a string).

For example 0.666666667 to be displayed as 1/1.5.

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

##### Well-known Member
Go to Number format, Select Fraction, then Select up to two digits or three digits based your data.

#### Attachments

• 1233.jpg
72.1 KB · Views: 4

#### Formula11

##### Board Regular
Yes I knew about that. But it has to be "1/something".

##### Well-known Member
Use fraction and formula to extract right section at text format & Use it . Example at uploaded file
Bank Statements (1).xlsm
ABCDE
1
22/31/1.52/328.66666667
3
Sheet3
Cell Formulas
RangeFormula
B2B2=1&"/"&(1/A2)
C2C2=1/RIGHT(B2,LEN(B2)-FIND("/",B2,1))
D2D2=C2*43

#### Rick Rothstein

##### MrExcel MVP

You can use this formula (similar to maabadi's formula) in cell B2...

="1/"&1/A2

Yes, the value is text, but if you involve that in a mathematical expression, Excel will perform the division before using it in the mathematical expression. So, if as maabadi suggested, you wanted to multiply it by 43, just do it directly (you don't need the string manipulations maabadi showed)...

=43*B2

#### Formula11

##### Board Regular
Thanks Rick.
I get "#VALUE!" when I use 1/1.5 in a formula though.

#### Rick Rothstein

##### MrExcel MVP

What was the formula?

The formula was:
=B2*2

#### Rick Rothstein

##### MrExcel MVP
Whoops! I am wrong... Excel won't perform the math like I had said. That is what happens when I try to rush an answer... I referenced the wrong cell in my all-too-rushed test before posting. You will have to do the text manipulation that maabadi posted. Sorry for any confusion I may have caused. However, you can use the formula for cell B2 that I posted as that works and has one less concatenation than maabadi's formula for that same cell.

Last edited:

#### Formula11

##### Board Regular
OK thanks for confirming.

Replies
4
Views
120
Replies
10
Views
615
Replies
6
Views
193
Replies
3
Views
442
Replies
4
Views
118

1,127,009
Messages
5,622,143
Members
415,880
Latest member
Bruce0203

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

### Which adblocker are you using?

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

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