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=3

and when I type

2 apple + 3 banana

I want

13.

message edited by onionoh


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

Reply ↓  Report •


✔ Best Answer
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 1

Start 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.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#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 fairly
complicated 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.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#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 need
rock + 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...


Reply ↓  Report •

#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 orange

If it will be more, is there a maximum number of items to deal with?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

Related Solutions

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

Reply ↓  Report •

#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".

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#6
September 28, 2016 at 10:30:10
✔ Best Answer
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 1

Start 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.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#7
October 3, 2016 at 14:38:57
Thank you so much !!!
XD
now I can play my game more efficiently!

Reply ↓  Report •


Ask Question