Formulas and Functions Help

aimeec   The more specific you are, the more likely you are to get a quick resolution.  Ideally, attached an Excel file that shows representative data, and explain how you would do the calculation manually.  In particular, what does it mean to "divide one column by another"?  Do you mean row-by-row?  And do you mean that each cell has the literally string "5,000-10,000" and "40-50", for example?

Assuming the latter and row-by-row division, the best we can do with that information is to divide the midpoint (average) of each range by the other.  Algebraically, that is the same as dividing the sum of each range endpoints by the other.  For example:

A2: 5,000-10,000

B2: 40-50

C2: =(LEFT(A2,FIND("-",A2)-1) + MID(A2,FIND("-",A2)+1,99))
/ (LEFT(B2,FIND("-",B2)-1) + MID(B2,FIND("-",B2)+1,99))

For that example, C2 is 166.666666666667.

However, that might not be correct mathematically for the actual data in each range.  For example, if the range 5,000-10,000 is comprised of 99 items at 10,000 and 1 item at 5,000, the average is 9950 = (99*10000 + 5000)/100, not 7500 = (5000+10000)/2.

So, if you have the original data that comprise each range, the better solution is =SUM(X1:X100) / SUM(Y1:Y100), where X1:X100 are the actual data between 5000 and 10000, and Y1:Y100 are the actual data between 40 and 50.