Is there a formula that adds, subtracts, multiplies, and or divides two digit numbers in a single cell?

[MrsFrankieH]

Are there formulas that will add, subtract, multiply, and/or divide data from two-digit numbers in a single cell?

I appreciate any help I can get. Thank you in advance.

 

[MrsFrankieH]

Hi Peter! I understand what you mean by "stored as text" now. Actually, I illustrated with text to show where the calculation comes from ("0" represents "10"). I think your formula will give me the results I want anyway!
Thank you so much!!!

 

[MrsFrankieH]

Hello Peter, thanks again for you help, I truly appreciate it. Peter, i would like to use numbers instead of texts (I didn't realize it would make a difference). Can I have a formula to change the formula to work with numbers instead? When i tried to use your formula using numbers, the calculations didn't work.
Thanks again Peter.

 

[Rick Rothstein]

Hello Peter, thanks again for you help, I truly appreciate it. Peter, i would like to use numbers instead of texts (I didn't realize it would make a difference). Can I have a formula to change the formula to work with numbers instead?

Peter will probably have a different approach (so you should check back to see what he posts when he comes back on), but a quick fix is force your numbers to two digits (when evaluating the "left" digit) using the TEXT function. Here are Peter's formulas with that idea implemented...

F3: =RIGHT(LEFT(TEXT(F1,"00"))+RIGHT(F1))+0
F4: =ABS(SUBSTITUTE(LEFT(TEXT(F1,"00")),0,10)-SUBSTITUTE(RIGHT(F1),0,10))
F5: =IF(FIND(0,TEXT(F1,"00")&0)>2,RIGHT(LEFT(TEXT(F1,"00"))*RIGHT(F1))+0,"")
F6: =IFERROR(MAX(LEFT(TEXT(F1,"00")),RIGHT(F1))/MIN(LEFT(TEXT(F1,"00")),RIGHT(F1)),"")

 

[Peter_SSs]

There are still issues that are not clear about your requirements and expected results.

1. For example, you said ...

Yes, "0"s are treated as 10 ...

.. but your examples indicate that is not the case for all circumstances. If it was, then for division, wouldn't 20 mean 10 divided by 2 = 5? In your example you gave "" as the result for 20 for division.

2. Which brings me to the question about the blank cells in your original sample results. Are they really supposed to be blank given that for multiplication say, 20 would be 2 * 10 = 20 and bringing that back to 1 digit would be 0 not blank. Can you explain further about that?

3. For division you now say ...

...for division, as long as the answer is whole, any formula will do. If there's a division where the number has a "." in it (i.e. 3.4 or something) that would show as 0

Can you confirm that
a. 37 should result in 0 (not blank) because both 3/7 and 7/3 result in remainders
b. 63 should result in "" or 0 (which one?) because one of 3/6 and 6/3 results in a whole number? That is, we have to check two separate calculations to determine the result for division?

 

[MrsFrankieH]

Peter will probably have a different approach (so you should check back to see what he posts when he comes back on), but a quick fix is force your numbers to two digits (when evaluating the "left" digit) using the TEXT function. Here are Peter's formulas with that idea implemented...

F3: =RIGHT(LEFT(TEXT(F1,"00"))+RIGHT(F1))+0
F4: =ABS(SUBSTITUTE(LEFT(TEXT(F1,"00")),0,10)-SUBSTITUTE(RIGHT(F1),0,10))
F5: =IF(FIND(0,TEXT(F1,"00")&0)>2,RIGHT(LEFT(TEXT(F1,"00"))*RIGHT(F1))+0,"")
F6: =IFERROR(MAX(LEFT(TEXT(F1,"00")),RIGHT(F1))/MIN(LEFT(TEXT(F1,"00")),RIGHT(F1)),"")

Thank you Rick!! For some reason I'm still getting errors. I'm using "numbers" instead of texts.

 

[MrsFrankieH]

There are still issues that are not clear about your requirements and expected results.

1. For example, you said ..... but your examples indicate that is not the case for all circumstances. If it was, then for division, wouldn't 20 mean 10 divided by 2 = 5? In your example you gave "" as the result for 20 for division.

2. Which brings me to the question about the blank cells in your original sample results. Are they really supposed to be blank given that for multiplication say, 20 would be 2 * 10 = 20 and bringing that back to 1 digit would be 0 not blank. Can you explain further about that?

3. For division you now say ...Can you confirm that
a. 37 should result in 0 (not blank) because both 3/7 and 7/3 result in remainders
b. 63 should result in "" or 0 (which one?) because one of 3/6 and 6/3 results in a whole number? That is, we have to check two separate calculations to determine the result for division?

Hello Peter,
Thank you for letting me clarify. I’ll do my best.

1. You’re right,”0”s are treated as “10” only when the numbers are single numbers (1 through 9). It helps me visualize better for subtraction. Take for example 8, when I see the number as “08” in my mind it’s “10 – 8” which would be “2”.

I think I understand the confusion about division. I’m sorry for the poor example I gave.

1. In your division example “20” would calculate to “5” (0 is 10).
2. Yes, if there are any calculations with remainders, they would be blank.

Yes, a number that would have a remainder in division, like 37, would result in a blank however a number like 63 divided would result in 2.
Peter thank you for pulling it out of me. Sometimes I struggle to express myself especially when it comes to calculations and thank you again for your patience.

 

[Peter_SSs]

1. You’re right,”0”s are treated as “10” only when the numbers are single numbers (1 through 9).

1. In your division example “20” would calculate to “5” (0 is 10).

The first of these says use 10 instead of zero only when the numbers are single, but the second one uses 10 instead of 0 even when the number (20) consists of two digits.

 

[Peter_SSs]

For some reason I'm still getting errors.

Whenever that is the case, helpers cannot work without information
- What does the actual error say?
- Give some examples of the data, what the error result is and what the expected result is

 

[MrsFrankieH]

Hi Peter, I understand the confusion with that and logically, it's difficult to calculate.

The best way I can explain it is when there are single digits, to make them double digits by putting a 0 in front and then to calculate the two digits.
For example:

20 is 2 + 0 = 2
20 is 2 - 0 (10) = 8
02 is 2 + 0 = 2
02 is 0 (10) -2 = 8
In other words, 0's are 10 when subtracting or dividing.

 

[MrsFrankieH]

Ok Peter, thank you so much!! I'm going to create some examples and post them.
Much appreciation!!!