# Solved how can I use words to solve simple math questions on excel?

September 20, 2016 at 16:50:07
Specs: Macintosh
 I want to input a data ex) apple=2, banana=3and when I type2 apple + 3 banana I want13.message edited by onionoh

See More: how can I use words to solve simple math questions on excel?

September 28, 2016 at 10:30:10
 Here are 2 options for a "multiple cell" entry of your items. I am going to use 5 items as the max, you could use more or less.Option 1Start with this table in C1:D5``` A B C D E 1 apple 2 (Formula Here) 2 banana 3 3 orange 5 4 pear 1 5 lime 4 ```Place this formula in E1:=A1*IFERROR(VLOOKUP(B1,\$C\$1:\$D\$5,2,0),0) +A2*IFERROR(VLOOKUP(B2,\$C\$1:\$D\$5,2,0),0) +A3*IFERROR(VLOOKUP(B3,\$C\$1:\$D\$5,2,0),0) +A4*IFERROR(VLOOKUP(B4,\$C\$1:\$D\$5,2,0),0) +A5*IFERROR(VLOOKUP(B5,\$C\$1:\$D\$5,2,0),0)Enter your values and items in A1:B5. e.g.``` A B C D E 1 4 pear apple 2 (Formula Here) 2 1 orange banana 3 3 2 apple orange 5 4 pear 1 5 lime 4 ```The formula will do a VLOOKUP of each item and then do the math to get the total value. You will notice that the entries I made in A1:B3 are not in any particular order, they don't have to be.Option 2 This options eliminates the need to enter the items in Column B. All you need to do is enter the values in Column A. The values must line up with the specific item.Start with this:``` A B C D 1 apple 2 (Formula Here) 2 banana 3 3 orange 5 4 pear 1 5 lime 4 ```Enter this in D1:=A1*VLOOKUP(B1,\$B\$1:\$C\$5,2,0)+A2*VLOOKUP(B2,\$B\$1:\$C\$5,2,0)+A3*VLOOKUP(B3,\$B\$1:\$C\$5,2,0)+A4*VLOOKUP(B4,\$B\$1:\$C\$5,2,0)+A5*VLOOKUP(B5,\$B\$1:\$C\$5,2,0)Now, just put your values next to the items you need:``` A B C D 1 2 apple 2 (Formula Here) 2 banana 3 3 1 orange 5 4 4 pear 1 5 lime 4 ```It's interesting that the "easiest" of all 3 options (these 2 and the 1 from the previous post) is Option 2 here. It is not only easier for the user since all you have to do is enter the values next to the items, but it also happens to use the least complicated formula.

#1
September 21, 2016 at 06:41:48
 This is not a "Simple Math Question" as far as Excel is concerned.If you plan to enter this string in a single cell then you are asking for a fairlycomplicated formula or macro. The string would have to be parsed to extract the "words", then look up the words in a table to find the associated value, then determine what kind of operation you want to perform (based on the operator entered) then finally perform the operation.Even if every string was of the same "format" as your example:(number, word, plus sign, number, word) the formula would be fairly complicated. If you plan on adding a third word and/or other operators, you are looking at something pretty complex.If these values will be in separate cells (or can be parsed into separate cells) things may get a little easier, but if there is no consistency in the format/length of the string, things get very complicated again.Maybe if you explained why you want to do this, we could offer a method/process that might work for you.

#2
September 22, 2016 at 22:06:33
 um.. its for a game.....like mine craft.ex) if I want to make a window I will need glass + wood and to make glass I will needrock + fire(something like that I don't really know minecraft)and I wanted to make a chart with every single items with total consumed time and total value and total money i will make in return.lol so if this is wayyyyy out of my league I can simply give up...

#3
September 23, 2016 at 06:27:19
 You didn't address the issues I mentioned in my response, so I'll ask the questions directly:A - Will you be entering this string in a single cell e.g ``` A 1 2 apple + 3 banana```Or will you be entering this string across multiple cells e.g ``` A B C D E 1 2 apple + 3 banana```B - Will the operation always be "addition', i.e. a plus sign?C - Will it always be just 2 items or might you have more? e.g.2 apple + 3 banana + 1 orangeIf it will be more, is there a maximum number of items to deal with?

Related Solutions

#4
September 27, 2016 at 17:08:19
 A- single cell.B- comma..? ( , ) but I could use + if that helpsC- might be more.maximum... not sure.probably something less then 5 kinds of items

#5
September 28, 2016 at 08:52:54
 OK, just to show you what we are up against, I came up with a formula that will work for your "single cell, 2 item" example. I then came up with an alternative that will work for more than 2 items, but it can't be used with a "single cell" entry. I am going to post the alternative suggestion in a separate response just for clarity.For the "single cell, 2 item" entry, the following formula seems to work, but these rules must be followed:1 - There can only be 2 items. While it's possible to create a formula that will work with more than 2 items in a single cell, I'm not even going to try - you'll see why later. I'll leave that up to you to try if you want.2 - There must be 2 items separated by a + sign, e.g. "2 apple + 3 banana" will work. "2 apple" will not.3 - The "items" can only be a single word, e.g. "apple " will work. "red apple" will not.4 - There can not be any trailing spaces, leading spaces or extra spaces within the string. Just the single spaces as used in your example.OK, with all that said, here is what I came up with.Start with your string in A1 and the following table in C1:D5``` A B C D 1 2 apple + 3 banana (Formula Here) apple 2 2 banana 3 3 orange 5 4 pear 1 5 lime 4 ```Enter this formula in B1 and you should get 13:=MID(A1,1,FIND(" ",A1)-1) * VLOOKUP(MID(A1,FIND(" ",A1,1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1,1)-1),\$C\$1:\$D\$5,2,0) + MID(A1,FIND("+",A1)+1,FIND("^",SUBSTITUTE(A1," ","^",4))-FIND("+",A1)) * VLOOKUP(MID(A1,FIND("~",SUBSTITUTE(A1," ","~",4))+1,LEN(A1)),\$C\$1:\$D\$5,2,0)As you can see, it takes a pretty complex formula to deal with just 2 items. In order for it to work more - especially with a varying number of items - it would become ridiculously cumbersome, although not impossible.In my next response, I'll offer an alternative than can be used if you are willing to use multiple cells for your "items".

 Here are 2 options for a "multiple cell" entry of your items. I am going to use 5 items as the max, you could use more or less.Option 1Start with this table in C1:D5``` A B C D E 1 apple 2 (Formula Here) 2 banana 3 3 orange 5 4 pear 1 5 lime 4 ```Place this formula in E1:=A1*IFERROR(VLOOKUP(B1,\$C\$1:\$D\$5,2,0),0) +A2*IFERROR(VLOOKUP(B2,\$C\$1:\$D\$5,2,0),0) +A3*IFERROR(VLOOKUP(B3,\$C\$1:\$D\$5,2,0),0) +A4*IFERROR(VLOOKUP(B4,\$C\$1:\$D\$5,2,0),0) +A5*IFERROR(VLOOKUP(B5,\$C\$1:\$D\$5,2,0),0)Enter your values and items in A1:B5. e.g.``` A B C D E 1 4 pear apple 2 (Formula Here) 2 1 orange banana 3 3 2 apple orange 5 4 pear 1 5 lime 4 ```The formula will do a VLOOKUP of each item and then do the math to get the total value. You will notice that the entries I made in A1:B3 are not in any particular order, they don't have to be.Option 2 This options eliminates the need to enter the items in Column B. All you need to do is enter the values in Column A. The values must line up with the specific item.Start with this:``` A B C D 1 apple 2 (Formula Here) 2 banana 3 3 orange 5 4 pear 1 5 lime 4 ```Enter this in D1:=A1*VLOOKUP(B1,\$B\$1:\$C\$5,2,0)+A2*VLOOKUP(B2,\$B\$1:\$C\$5,2,0)+A3*VLOOKUP(B3,\$B\$1:\$C\$5,2,0)+A4*VLOOKUP(B4,\$B\$1:\$C\$5,2,0)+A5*VLOOKUP(B5,\$B\$1:\$C\$5,2,0)Now, just put your values next to the items you need:``` A B C D 1 2 apple 2 (Formula Here) 2 banana 3 3 1 orange 5 4 4 pear 1 5 lime 4 ```It's interesting that the "easiest" of all 3 options (these 2 and the 1 from the previous post) is Option 2 here. It is not only easier for the user since all you have to do is enter the values next to the items, but it also happens to use the least complicated formula.