My Fractions

Hi everyone,

Today, I attempted to apply a lot of the helpful knowledge I have gathered from this forum so far... I had a special need involving fractions, and after fiddling with the formula I got here before, I came up with this:

=IF(MID(TRIM(TEXT(A1, "# ?/?")),2,1)="/",IF(LEFT(TRIM(TEXT(A1, "# ?/?")),1)="1","One "&VLOOKUP(RIGHT(TRIM(TEXT(A1, "# ?/?")),1)+0,{1,"Onth";2,"Half";3,"Third";6,"Sixth"},2,FALSE),VLOOKUP(LEFT(TRIM(TEXT(A1, "# ?/?")),1)+0,{1,"One";2,"Two";5,"Five"},2,FALSE)&" "&VLOOKUP(RIGHT(TRIM(TEXT(A1, "# ?/?")),1)+0,{1,"Onths";2,"Halves";3,"Thirds";6,"Sixths"},2,FALSE)),IF(MID(TRIM(TEXT(A1, "# ?/?")),4,1)="/",VLOOKUP(LEFT(TRIM(TEXT(A1, "# ?/?")),1)+0,{1,"One";2,"Two";3,"Three";4,"Four";5,"Five"},2,FALSE)&" and "&IF(MID(TRIM(TEXT(A1, "# ?/?")),3,1)="1","One "&VLOOKUP(RIGHT(TRIM(TEXT(A1, "# ?/?")),1)+0,{1,"Onth";2,"Half";3,"Third";6,"Sixth"},2,FALSE),VLOOKUP(MID(TRIM(TEXT(A1, "# ?/?")),3,1)+0,{1,"One";2,"Two";5,"Five"},2,FALSE)&" "&VLOOKUP(RIGHT(TRIM(TEXT(A1, "# ?/?")),1)+0,{1,"Onths";2,"Halves";3,"Thirds";6,"Sixths"},2,FALSE)),VLOOKUP(A1)+0,{1,"One";2,"Two";3,"Three";4,"Four";5,"Five"},2,FALSE))))

This does exactly what I need it to do, but I guess my first question is: Is there a better way to do what I just did? I'm just starting to figure this stuff out, so I feel like I am going about things the long way around sometimes...

Then comes my next problem. I then had to fit this formula into a sentence in a cell... sort of like "Today's magical Number is [the formula goes here], and what a nice Number it is indeed!" But when added to the sentence, it gave me the "Formula too long" error. So I need to figure out a way to fit this in there... I thought maybe I could make a function that would do all this, so that I could enter "FunctionName(A1)" instead of the whole formula... is that even possible, or am I having confused dreams? I'm doing my best to take in all of the information I can find, but I do get confused easily... In my attempt to make a function that would work for me, I got stuck really quickly where I couldn't get past the point of getting the function to see the fraction as a fraction... it kept treating the fraction like a decimal number (0.3333333). I formatted the cell as "Fractions", and also tried playing with the "Format(---)" thing in VBA, but I think maybe I don't know how to use the "Format" thing in VBA properly... I keep failing, and I'm not sure what I should be doing now... So I have decided to make "cry hopelessly" my Plan B, and my Plan A is to come here and hope that someone can tell me what I should do.

Thank you for taking the time to read my post... I know I ask for a lot of help, but I promise I will keep improving my Excel skills so that I can be the helper some day!

Thank you for your reply, Bryan Hessey!

I do realize that the formula I posted here is not the original one. I had to take the original and change it a bit to get it to do what I needed for this specific case. I needed it to be able to do whole number/fraction mixes as well as fractions on their own or whole numbers on their own, and the possibilities are limited, so I was able to take out some of the formula.

I'm not sure how to get the formula to show in a paragraph without doing
="paragraph text "&[formula]&" paragraph text"
In which case, the whole thing becomes a formula, and excel says it's too long. Are you saying that I should put this formula in another cell and just have the formula part of the paragraph refer to that cell?

Is TEXT usable in VBA? I tried using it just as it appeared in the formula, but I had troubles... maybe it was another part of my function that wasn't clicking, and I just thought it was the TEXT part... I get confused about what you can or can't do because it seems like you can't just type a formula into VBA like you would into the formula bar...

It would be helpful if you repaste your formula, and also put it inbetween [CODE ]Please Login or Register to view this content.[/CODE]

By doing this, the formulas tend to paste better (extra spaces tend not to get added in the wrong places).

Scott

Thank you for the pointer, Maistrye. I will keep that in mind from now on!

Bryan Hessey,
I think I am getting confused? Pasting my formula onto the end of a paragraph puts the whole formula there as text, like you said... I need the formula to be in the same cell as the paragraph text, but I need it to function as a formula so that the result will be displayed within the paragraph. So I put it in there as
=" [my paragraph text] "& [my formula] &" [my paragraph text]"
except that excel is telling me that this makes a formula that is too long... and that's why I thought maybe I could solve this problem by creating a function... only that I started running into roadblocks when trying to create a function.

Could you perhaps upload an example in an excel file with this formula? Sorry to harp on this, but since you said it's doing what you want, it's rather useful. If you do it this way, you will need to ZIP the excel file before you upload it. If it's your first time uploading, it will be more fun. :-)

One comment though. It should probably be possible to put your vlookup outside of your formula (at least, the range of lookup values). This will make it more robust, easier to edit, and shorter.

I'd recommend you put the list on another page, and then create a Named range to reference it, and use that Named range in your formula.

ie. VLOOKUP(... , WholeNum, 2, False)

Where WholeNum refers to your range on the other page with the correspondance. You can do similarly with the denominator.

Scott

Sorry Maistrye! Upon reading your post, I re-read your initial post, I realized that you weren't just teaching me about the [CODE ]Please Login or Register to view this content.[/CODE]

I hope that works...
Your idea about putting a named list on another sheet in order to shorten the formula sounds like a plan! I was also thinking, though, after Bryan Hessey's post, that I could put this formula just once on another sheet, then reference the result in the paragraph... I was thinking this because this is the way it's being used:

WS1.A1 = 1 1/6 [this is what someone inputs]
WS2.A1 = Put Forumla Here [this cell would display "one and one sixth"]
Then, I need to use this WS2.A1 result in different paragraphs of different sheets...
WS3.A1 = Paragraph text one and one sixth Paragraph text
WS4.A2 = Paragraph text one and one sixth Paragraph text
WS5.A3 = Paragraph text one and one sixth Paragraph text

The WS1.A1 input only happens once per workbook, and as of right now I am trying to squeeze the whole formula in with the paragraph text on Worksheets 3, 4, and 5... so I really think this way might make the formula used in each area even shorter... ... unless I'm missing something... I usually miss something... what do you think?

Wow, confusing!! hehe!

I did manage to understand your very first question, though! The answer is yes, I have used Names before... just never to that extent! Sounds like fun, though... I'll go give it a shot after I post this.

As it turns out, I use a Name in this very situation too! The fractions that can be inputted into A1 are limited to certain options, and I made a list on another sheet and named the list so that I could make a drop-down box in A1! ... that's all though... I didn't think to use Names for other things.

One question I do have off the bat... you said:

In other words, if someone put in 3.5 into A1:

MyFraction: "7/2"
MyDenominator: 2
MyNumerator: 7
MyWhole: 3
MyRemainder: 1

...does that mean that the input in A1 would have to be done in decimal format instead of fractions?

No... the numbers can be fractions or decimals, depending on how you format it. I just said 3.5 because I type my example in as that... 3 1/2 works too. Or 7/2. Or whichever other format you preferred.

Scott

Hi Maistrye,

I tried the Names thing... I didn't know it could be used like that. I had lots of fun! I did run into a few "#N/A" and "#REF!", and I was hoping you could help me figure out why? I copied and pasted your formulas, and named them exactly what you named them... so I'm not sure what is getting stuck.

This is what I have displaying so far:

A1: 1/6
MyFraction: 1/6
MyDenominator: 6
MyNumerator: 1
MyWhole: 0
MyRemainder: 1
MyNumText: 0
MyFracTextSingle: 1
MyFracTextMultiple: 1
MyWholeText: #REF!
MyFractionText: #N/A
MyText: #N/A

What did I do wrong?

Thank you for your help!

Hmmm, I plugged them in again from the post and it worked fine... probably not what you wanted to hear.

You might want to go back to the the named ranges definitions and look at the formula you have for MyWholeText. Usually you get the #REF! error if a cell referenced has been deleted... but the formula should have only referred to other named ranges. If there was a mistake with the naming, you'd have got a #NAME? error. It is possible that when you typed in the formula, you forgot to hit enter... and the cell that Excel 'guesses' to put in there initially might have been deleted at some future point.

For the MyText formula... the error is as a result of the MyFractionText, so it should be resolved when MyFractionText is fixed.

For the MyFractionText formula: #N/A usually results when you do a VLOOKUP and the index value cannot be found. So, for your example, if you put in 1/5, the 5 would not be located in the table, and would return #N/A. This shouldn't happen with the 6 from 1/6 though... There are two things I can think of to check:

(a) Check MyFracTextSingle (and MyFracTextMultiple) to make sure they are as they're supposed to be.

You don't actually have to look at the formulas to do this. Simply put the following formulas on your page:
=VLOOKUP(1,MyFracTextSingle,2,false)
=VLOOKUP(2,MyFracTextSingle,2,false)
=VLOOKUP(3,MyFracTextSingle,2,false)
=VLOOKUP(6,MyFracTextSingle,2,false) <-- Editted.... I put 4 instead of 6 the first time. :-)
If any of these does not return the text, then this is where you need to make your correction. (Do it also with MyFracTextMultiple)

(b) Check MyFractionText. This might take more effort... the easy way might be to delete and re-create the name, copying the formula again from above. The more difficult way would be to go to the named range and check to make sure you put the correct name as the first parameters in each of the VLOOKUPs. (When I was making this up the first time, I didn't, and I got the #N/A error... I think I ended up using MyNumerator instead of MyRemainder, which meant for 7/2, I was trying to find 7 in a table where the maximum value was 6.)

The actual cause of this error for you is sort of random guessing on my part, so it might take a bit more work. If you could rule out the other problems, such as that with MyWhole and make sure to verify that MyFracTextSingle/Multiple return the correct values, it would eliminate some possibilities, hopefully making it easier to isolate the exact problem.

Scott

It does narrow down at least some of the problems.

The errors in MyText and MyFractionText are being caused by a problem with the MyFracTextSingle/Multiple. You might also have a problem with MyNumText.

The only thing I can think of is that perhaps you put all semi-colons or all commas instead of alternating them in these arrays. When I put all semi-colons, I got a #REF! error, and when I put all commas, I got a #N/A error.

If that doesn't seem to fix it, could you open up a blank workbook, and re-do everything? Then you could attach the file here and I could take a look at it to see what was causing the problem.

Scott

Not saying *need* to do it again. Depends on what's in your file. You could just make a copy of it and cut out the extraneous stuff.

Scott

I really hope the problem isn't just something silly...

Ok, I think I know what the issue is.

It has to do with how the arrays are specified. The way I was thinking about them (which you'll see in the attached document) is slightly different from the way you implemented it.

Essentially, the difference is that in your case, the name MyFracTextSingle is a reference to the location of the array, whereas what I intended was that MyFracTextSingle be the array. (The distinction is sort of subtle.) What this means is when Vlookup uses your reference, it finds one cell in the table it's looking at, and can't find a match, so it returns an error.

The changes I've made to your spreadsheet:

1) I added AFracTextSingle, AFracTextMultiple, and ANumText as names so you can see what I meant (note that this is not used anywhere... it's just there so you can see the difference). (Insert->Name->Define and select one of them) You can delete them once you've looked at them -- they're just there as an illustration.

2) I modified your sheet so that the lookup ranges are actually on your spreadsheet. You'll see them on the side. The names MyFracTextSingle, MyFractTextMultiple, and MyNumText all refer to those instead.

NOTE: I adjusted MyWholeText slightly:
MyWholeText: =IF(MyWhole=0,"",VLOOKUP(MyWhole,MyNumText,2,FALSE))

3) I didn't realize you had macros. :-) This presents the useful alternative of creating a User Defined Function to the task. I stuck it in. All you have to do to use it is put this wherever you want:

=FractionToWords(A1)

It acts like a regular function. (Only in the files you put it in, however)

To view it, press Alt+F11, it is in the Module called FractionFunction. I didn't make it really robust... it only works up to 9 and 8/9. However, that covers the range you were looking for.

If you want to use the User Defined Function in another file, you'll have to copy it to that other file.

Scott

1) I checked out the new names you defined, and I see what you mean... that makes sense!

2)I see your lookup ranges off to the side... so we can use either MyFracTextSingle like this, or the new AMyFracTextSingle that you defined, right? They are the same thing, done differently?

The modification to the MyWholeText also makes things look nicer.

3)I didn't realize I had left that module in there! >< That's so embarrassing... That was me trying to figure out how some of the words in VB work. I would record a macro, then change some of the words to see what happens.

Your User Defined Function is awesome! I want to be able to do that some day! I've gotten a few other User Defined Functions from the helpful people of this forum, and I always study them, trying to figure out how they work.. but I just don't understand some of the terms that are used often... like "Dim" and "As String" or "As Long"... is there a site somewhere that would explain the usage of these terms to me?

Thank you so much, Maistrye. This particular thread has been unreal. I have learned so many new things this time! I really appreciate you taking the time to help me!

Yup, they're the same, just done differently.

If you're going to put the names like I did for AFracTextSingle, I'd recommend you do it with all the formulas, not just those 3. That is your choice. To use them instead of the list on the sheet, just change MyFracTextSingle (etc.) so it has the same value as AFracTextSingle... that way you don't have to change all the references in the other names.

As to learning what they mean...

I'd recommend you start another thread and ask people for good links to websites for beginners wanting to learn VBA.

Also, you can go to the public library and get a book that covers some of the basic aspects. (I don't like buying beginner books -- they're too expensive and you'll grow out of them -- it is a preference thing though. I prefer to get the beginner ones from the library, and then when i know more, buy a more advanced book. This helps avoid buying a bad book on your first try too.)

Sometimes I find where I live that the big box grocery stores tend to occasionally have clearances on their books, particularly older technical ones. It might be a place to pick up something for $5-$10. It's a case of watching until you see one you can use.

As to the terms you mentioned,

DIM MyVariable as String

"DIM" is the first word on the line whenever you want to create a variable to use. "MyVariable" is the name you want to use. "as String" just says that the variable is a string of text.

"as Long" is another type of variable that refers to numbers that are integers.
"as Integer" is similar to "as Long", except it has a smaller range of allowable numbers.

There are many others, but those are some of the basics.

Scott

Great advice, Maistrye! I'll go post in the Programming forum here to ask if anyone has links they want to share. I'll also check out my local public library and big box stores when I get some free time. Thank you for everything!