0

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(A2<5,B2>=5),J2,IF(AND(A2>=5,B2<=19),1,IF(AND(A2>19,B2>19),0,IF(AND(A2<=19,B2>19),J2,IF(AND(A2<5,B2<5),0)))))

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.

Here is an example linked below ( I tried using the format text as a table link, but when I added it to the post, the formatting was completely off). Everything works well until you get to the upper range. If you look at the 17-22 row, the output is 3. If I can fix the formula, the percent included in the desired range should be 0.5 and because the min and max values cross over the 19 mark, the output should display 0.5 as well. I think the rest of the formula works fine, but I'm not sure how to define the percentage inside of the 5-19 range. data set example

  • 1
    Some diagrams of your data would help us understand your question. Please edit your question to include some example (mock) data (before and after). See Format Text as a Table for a web utility that will help you to create a nice data table you can paste into your question. – DavidPostill Apr 30 '16 at 15:06
  • 1
    "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." shouldn't that be .5 (50%) as 1 value is inside and 1 outside? – DavidPostill Apr 30 '16 at 15:08
  • user6274972 is using the max/min values to describe an interval and he/she wants to find the area of intersection with a target interval, so 25% is correct. – picobit May 1 '16 at 6:25
  • Updated the post, tried the format text as a table link, but when I added it to the post, the formatting was completely off. Thanks again for the help! – Corey May 1 '16 at 9:20
0

Was provided this answer on another forum and it works as desired. Thank you to all who replied for your help and input though. This was one I wasn't going to be able to solve on my own. Thanks again.

=MAX((MIN(B2,19)-MAX(A2,5)+1),0)/(B2-A2+1)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.