Add Text prior to fraction

Hi All,

I have cell BF2 that reads "Approximately -" without quotations.
In cell BG2, I have 5/8"
In cell BH2, I wish it to read (Approximately - 5/8") without parentheses

The cell BG2 is in a custom format (?/8 "''") for 0.625, which becomes 5/8", so not sure if this matters.

I have over 80,000 rows to fill, and I have tried all I know, with no success. Any help would be appreciated. Thanks

I forgot to add, that there are several rows that will have various measurements, such as (1/4", 5/8", 1 1/4", 2", etc.), if this matters?

Try this:

=BF2&TEXT(BG2,"# ?/?")

Looks like both of you hit on this at the same time, lol. Hey, the more help I get is awesome, so all good! Both of your recommendations work, however, two things. First, there is no " symbol following the measurement. Secondly, will either of these work with ALL measurements, i.e., 1/4", 1 1/2", 5/8", 3", etc.?? I can probably filter that column by measurement, to adjust, but would be nice if there was a one all solution, for all measurements. Can we get that " symbol in there? Thanks guys

Thanks Andy, your input seemed to get the inch symbol made inclusive. Thanks! However, if I were to have a 1/4", this formula would result in 2/7", which is not an accurate measurement. Somehow, I just don't believe that Excel has the ability to accurately determine a proper measurement, based off a inch decimal measurement, such as 0.3, which ends up being 2/7". Am I wrong? Or is this something that requires VBA?

It might be a bit slow to calculate with ~80k rows of data, but this method will adjust the fraction to 1/2, 1/4, 1/8 etc. if the decimal is an exact equivalent.

Anything else, so 1/3, 2/7, etc will be displayed as ?/128

=BF3&TEXT(BG3," ?/"&IFERROR(INDEX({128,64,32,16,8,4,2},MATCH(2,1/(({128,64,32,16,8,4,2}*BG3)=INT({128,64,32,16,8,4,2}*BG3)))),128)&"\""")

Precision can be adjusted by adding or removing elements from the 3 arrays, all of which should be identical.
The value iferror is the fallback denominator, used when there is no exact match.

Thanks once again! The formulas presented, some work, but does not maintain consistency on results.
Jason, that was an amazing formula, and well thought out for sure. It might have some use, but it didn't pan out well
on some measurements.

So I am attaching a sheet that has the exact measurements I am faced with (in decimal format), which are to be
represented as fractions. In the sheet, I describe the measurements we are looking for, just as a carpenter would
read the ruler. Please use your submitted formulas on this sheet, to see for yourself the results. I thought this
would be more helpful by showing. Thank you!

Originally Posted by MrShorty

Why should 0.3 be displayed as 1/4 instead of 2/7? 2/7 (0.286) is clearly closer to 0.3 than 1/4 (0.25). When you format as fraction with one digit in the denominator, Excel will choose the closest fraction (with one digit in the denominator) to the actual value -- in this case 2/7.

Perhaps you are looking for "the nearest 1/8th"? You could use "?/8" which will display everything with an 8 in the denominator. So 0.5 would display as 4/8. 0.3 would display as 2/8.

For "fractions to the nearest 1/8 but also reduced to simplest form", you will probably need an MROUND() function [=MROUND(number,1/8)], then format that as ?/?. Of course, the MROUND function changes the actual number (from 0.3 to 025, for example).

Does that help any?

Yes that does help sir, and I do appreciate your input. However, I need for the customer to see the fractions, just as a carpenter would see them. A ruler will not give you 7ths, so yes, we would need to round that up or down, in order to provide a close match to the decimal measurement provided. I have attached a sheet, showing a sample, as well as brief explanation on the objectives. Thanks for your input!

Originally Posted by MrShorty

It is still not clear to me what your smallest interval will be. Most rulers will be either 1/8 or 1/16, but I have seen 1/32 rulers. I am still inclined to use an MROUND() [or maybe CEILING() if you always want to round up] function MROUND() help file: https://support.office.com/en-us/art...b-d2d5b3baf427

I am also not sure if you need the "approximately " to be a part of the text string not. Remember that number formatting can be used to display that kind of text, but it does not become a part of the underlying cell value.

Assuming 1/8 inch precision and number formatting is acceptable for displaying the additional text, I would:
1) Add a helper column with =MROUND(A2,1/8)
2) Format this column as "approximately "# #/#\"

Will that work for you?

Thank you for your input sir. I will experiment with your provisions. As for measurement, if you go to a hardware store (USA), and pick up a 12" ruler, you will not see 32nds, you will see
1/16 all the way to 15/16
1/8 all the way to 7/8
1/4 all the way to 3/4
1/2
1"

These are the (common) measurements that the designers use in my industry. By use of (Approximately), this tells the customer exactly that.
If an item is not (exactly) 3/8" and it's really 5/16", well it's (Approximate)
Now if we can get those decimals to be EXACT, GREAT! I love it! But I can only work from the decimals provided, as you will see in
the example sheet provided. If by placing text in the cell, along with the fraction, is not good practice, then what is the better
solution? This information is going into a website, so once I have the correct formula, and it works, I then convert that cell to the value
only, and import to web. Thus far, I have seen great progress from a few here, but the formulas provided do not yet conquer
the fraction variations. Again, I will try yours to see if I can get better results.

The rulers that I have looked at vary with the precision of the scale, some show 32nds along the entire length, others start with 32nds but the precision is reduced to 16ths, and even 8ths over the length of the rule.

Best I can come up with, see how it looks with some real data. This should round to nearest 16th and display in the lowest form with the correct formatting.

=BF2&IF(BG2<1,""," ")&IF(MROUND(MOD(BG2,1),1/16)=0,TEXT(BG2,"?\"""),TEXT(BG2,"? ?/"&INDEX({16,8,4,2},MATCH(2,1/(({16,8,4,2}*MROUND(BG2,1/16))=INT({16,8,4,2}*(MROUND(BG2,1/16))))))&"\"""))

Originally Posted by jason.b75

The rulers that I have looked at vary with the precision of the scale, some show 32nds along the entire length, others start with 32nds but the precision is reduced to 16ths, and even 8ths over the length of the rule.

Best I can come up with, see how it looks with some real data. This should round to nearest 16th and display in the lowest form with the correct formatting.

=BF2&IF(BG2<1,""," ")&IF(MROUND(MOD(BG2,1),1/16)=0,TEXT(BG2,"?\"""),TEXT(BG2,"? ?/"&INDEX({16,8,4,2},MATCH(2,1/(({16,8,4,2}*MROUND(BG2,1/16))=INT({16,8,4,2}*(MROUND(BG2,1/16))))))&"\"""))

Jason = AWESOME!! I am sorry it took me a delay, as I am just getting back to work on this again. Your formula WORKED! Thank you so much sir!!

MrShorty's much simpler suggestion can be adapted to 1/16

Originally Posted by MrShorty

1) Add a helper column with =MROUND(A2,1/8)
2) Format this column as "approximately "# #/#\"

I did try this method previously, but using 1/128 as the accuracy level, which was still returning some denominators not fitting to your scale, I should have revisited this option when you told us that you only needed to go down to 1/16, had I done so, I would have reiterated MrShorty's post instead of revising my formula.

Formula =MROUND(A2,1/16)
Custom format "approximately -" # ##/##\"

Gives the same results as the long formula that I provided.

However, the long formula does not go to waste, there may be occasions when somebody needs fractions aligned to a specific scale where mround and formatting do not work as needed.