Display number as fraction 1/x

Formula11

Board Regular
Joined
Mar 1, 2005
Messages
233
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.
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,577
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Go to Number format, Select Fraction, then Select up to two digits or three digits based your data.
 

Attachments

  • 1233.jpg
    1233.jpg
    72.1 KB · Views: 4

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,577
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
Joined
Apr 18, 2011
Messages
36,858
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,858
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

What was the formula?
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,858
Office Version
  1. 2010
Platform
  1. Windows
Whoops! I am wrong... Excel won't perform the math like I had said.:mad: 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:

Watch MrExcel Video

Forum statistics

Threads
1,127,095
Messages
5,622,670
Members
415,921
Latest member
ExcelNoob28

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?

Disable AdBlock

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
Top