11-29-2020 09:21 AM
11-29-2020 09:21 AM
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
11-29-2020 10:09 AM - edited 11-29-2020 10:14 AM
@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:
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.