Formulas and Functions Help

Copper Contributor

Hi,

 

Not sure whether anyone will be able to help me but I am not that confident with Excel but did do work on it for 2 years previously so have some knowledge. However, I am helping out a friend with their spreadsheet as they have just collected some data. I have two columns which I'd like to divide one by the other, although they are values which are ranges, for example, a range of 5,000-10,000 to be divided by another range of 40-50.

Is there a way that I can divide one by the other to produce another range value as I am aware that by doing 5,000-10,00 / 40-50 will not give you one answer?

 

Hope someone can help me :) Thank you very much

3 Replies

@aimeec 

What is the expected finding from the calculation? With excel, you can not divide ranges together. Rather, a figure needs to be used, in the example below, I used the average of the ranges

adversi_0-1606671176538.png

 

Hi @aimeec 

 

You need to add helper columns to facilitate your work,
One column to have the average for Range1 and another one for the average for Range2, then you can Divide the average for range1 by the average for range2

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