Display number as fraction 1/x

Formula11

Active Member
Joined
Mar 1, 2005
Messages
431
Office Version
  1. 365
Platform
  1. Windows
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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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: 19
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Thanks Rick.
I get "#VALUE!" when I use 1/1.5 in a formula though.
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

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
Back
Top