I'm stumped.
I am arranging a data set that contains ranges of numbers that are specified by two columns, the minimum and maximum values (sometimes these values are the same). I am interested in the values from 5 to 19. Unfortunately, ranges from the data set do not always fit neatly in or out of this range I am interested in (5-19). If a row's range intersects these parameters(lies part inside and part outside of the 5-19 range), I want the formula's output to be the percentage of the value of the range that included in the desired range (5-19).
For example, if the values were 2 and 5, I would want the output from the formula to be .25 because of that range, 25% was included in my desired range. The same goes for to upper threshold, if the values were 19 and 22, or 18 and 21, I would want the output from the formula to be .25 because, again, 25% was included in my desired range.
If the range in the rows rests inside of my desire range (anything with a minimum value over 4 and a maximum value under 20), I would like the formula's output to be 1, and if both the minimum and maximum values are under 5, or both are over 19, I would like the output to be 0.
Right now I have this divided into 2 parts, one column was intended to provide my percentages if the range lies crossing the 5-19 threshold. I have found this to be my problem in that it only accounts for the lower threshold, and not the upper one.
=(B1-4)*(100/(B1-A1+1))*0.01
Column B is Max Value and Column A is Min Value
The next column is where I try to produce my intended value, which works, except when values cross the upper threshold (crossing 19), it outputs the C1 value(the result of the first formula), which is wrong for the upper range.
=IF(AND(A1<5,B1>=5),C1,IF(AND(A1>=5,B1<=19),1,IF(AND(A1>19,B1>19),0,IF(AND(A1<=19,B1>19),C1,IF(AND(A1<5,B1<5),0,IF(AND(A1<20,B1>=20,C1)))))))
I can direct the ranges that cross the upper threshold to display a different output of course, but for the life of me I can't figure out how to work the formula crossing the upper threshold.
Any thoughts or insights would be greatly appreciated.
برچسب:
نویسنده: استخدام کار