Using two seperate numbers located in the same cell.

[Terghan]

This forum has been excellent with helping me solve my excel issues, and so I have another question to ask.

I am currently working on a marketing report. I have certain cells calling for numbers from cells on a different sheet, and placing them in one single cell. Because the two numbers represent a high and a low, I have them displaying in the new cell like so;

1225 - 1420

Lets call those low and high square feet. I would like to be able to determine the price per square foot by dividing the number with the corresponding price, which may also look like this. To clarify, I have two cells, and they look a little something like this;

1225 - 1420
970 - 1220

I need to be able to get the price per square foot, and have it display properly. The correct answer would look like this;

0.79 - 0.86

Any help would be much appreciated.

Thanks in advance!

[NBVC]

If those are in A1:A2, then try:

=ROUND(LEFT(A2,FIND("-",A2)-1)/LEFT(A1,FIND("-",A1)-1),2)& " - " &ROUND(MID(A2,FIND("-",A2)+1,100)/MID(A1,FIND("-",A1)+1,100),2)

[Terghan]

Excellent, thank you very much. So, now, if one or both cells has only a single number in it, is there a way to alter that forumula to still work?

[NBVC]

How did I know it wasn't going to be that easy....

so if only one number appears in one cell and 2 numbers appear in the other cell, then how should the math be applied?

[Terghan]

The single number from one cell would need to be divided by both numbers in a cell with multiple numbers. I may have the solution though, let me try and write this out, and I'll let you know if it works or not.

[Terghan]

Alright, so I've come to terms with the fact that this will take a hot minute to get everything in place, but here is the preliminary formula I've written. It works, with the exception that for some reason it is not only returning the answer with only 2 decimal places, but instead the entire number. Not sure where I went wrong with that.

=IF(COUNTBLANK(B37:B38),"",IF(FIND("-",B38),ROUND(LEFT(B38,FIND("-",B38)-1),2)/B37&" - "&ROUND(MID(B38,FIND("-",B38)+1,100),2)/B37,""))

[Terghan]

Ignore that, I'm an idiot :P

Fixed.

[NBVC]

Great. Thanks for the feedback.

[Terghan]

I am going to leave this thread unsolved until I have completed the formula, at which point I will post it here and add the prefix.

[Terghan]

Alright, despite thinking I had successfully completed this, I have decided I have no idea what I'm doing. Here is what I am currently working with, if anyone can tell me what I'm doing wrong, I would really appreciate it.

IF(AND(FIND("-",B37),FIND("-",B38)),ROUND(LEFT(B38,FIND("-",B38)-1)/LEFT(B37,FIND("-",B37)-1),2)&" - "&ROUND(RIGHT(B38,FIND("-",B38)+1)/RIGHT(B37,FIND("-",B37)+1),2),IF(FIND("-", B37),ROUND(B38/LEFT(B37,FIND("-",B37)-1),2)&" - "&ROUND(B38/RIGHT(B37,FIND("-",B37)+1),2),IF(FIND("-", B38),ROUND(LEFT(B38,FIND("-",B38)-1)/B37,2)&" - "&ROUND(RIGHT(B38,FIND("-",B38)+1)/B37,2),"")))

[NBVC]

Does this work?

Please Login or Register  to view this content.

The above will round something like 0.999 to 1 without decimals...

If you always want 2 decimals, you can try:

Please Login or Register  to view this content.

[Terghan]

The first line is the one I used. Thank you very much for that, but I admit, I'd like to have a better understanding of why that is working. It seems to me as though I don't fully understand how you utilized the ISNUMBER function.

[NBVC]

If there is a number only in the cell, then ISNUMBER will give a result of TRUE. If there is anything in the cell like the dash, then the cell does not contain a "real" number so ISNUMBER will return FALSE. The +0 coerces a number that might be entered as text (either the cell is formatted as text or it is preceded with an apostrophe).....

So basically the easiest way to see if a cell has only one number in it is to use ISNUMBER() with the +0 coersion....

[Terghan]

I admit I'm still not certain I understand how the +0 works with the ISNUMBER function, but knowing that it essentially asks whether or not there is only one number in the cell allows me to see how the code is working in a general sense. Thank you for that.

[NBVC]

If you have a number in a cell, say A1, then go to cell B1 and enter =A1+0
You should get the same number

Now in A1, enter an apostrophe followed by a number, like: '10.2 this represents a text string.. but you should see that B1 still converts it to a number: 10.2

Now in A1 enter: 5.7 - 8.9... you will see that B1 gives a #VALUE error... because you are trying to add 0 to a definite text string..

So the ISNUMBER(A1+0) is simpy checking that what you have in A1, either a truly formatted number or a number that is formatted as text, is a single number... if there is a dash, then it can't coerce to a number, so you get an error, and ISNUMBER() returns FALSE

I hope that's better....

[Terghan]

Fantastic, that is exactly what I needed. Thank you very much for your help.