Display number as fraction 1/x

[Formula11]

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.

 

[maabadi]

Go to Number format, Select Fraction, then Select up to two digits or three digits based your data.

 

[Formula11]

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

 

[maabadi]

Use fraction and formula to extract right section at text format & Use it . Example at uploaded file

Bank Statements (1).xlsm
	A	B	C	D	E
1
2	2/3	1/1.5	2/3	28.66666667
3
Sheet3

Cell Formulas
Range	Formula
B2	B2	=1&"/"&(1/A2)
C2	C2	=1/RIGHT(B2,LEN(B2)-FIND("/",B2,1))
D2	D2	=C2*43

 

[Rick Rothstein]

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]

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

 

[Rick Rothstein]

What was the formula?

 

[Formula11]

The formula was:
=B2*2

 

[Rick Rothstein]

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.

 

[Formula11]

OK thanks for confirming.