Determining the correct income distribution based on several different ranges

[dmabs21]

Hi,

My problem is calculating the correct income range based on several other income ranges. The solution is listed in the all units row. I need a formula that will return the correct scenario. I find the figuring out the gaps is the biggest problem that I am having. My attempts have mostly missed a gap between 29000 and 32000.

There are up to 5 income ranges, that may either overlap or not at a given time. I provided an example below that shows two gaps, between the first range and the second range and the second range and the third range. I would like the answer to be based only the min and max columns. If there is an overlap, it should not double count. For example if the first range was 19000-28000 and the second range was 27000-32000, then the correct answer in the all units column for the 20000-29999 row would be 9999. The other problem is that in later rows its not an even distribution and it goes up from 9999 to 14999, 24999, and 49999.



[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Income cohort[/TD]
[TD]All units[/TD]
[TD][/TD]
[TD][/TD]
[TD]Range[/TD]
[TD]Min[/TD]
[TD]Max[/TD]
[/TR]
[TR]
[TD]0-9999[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]-[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]10000-19999[/TD]
[TD]999[/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD]19000[/TD]
[TD]26000[/TD]
[/TR]
[TR]
[TD]20000-29999[/TD]
[TD]8000[/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD]27000[/TD]
[TD]29000[/TD]
[/TR]
[TR]
[TD]30000-39999[/TD]
[TD]7999[/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[TD]32000[/TD]
[TD]44000[/TD]
[/TR]
[TR]
[TD]40000-49999[/TD]
[TD]4000[/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD]-[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]50000-59999[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]60000-74999[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]75000-99999[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]100000-124999[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]125000-149999[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]150000-199999[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]200000+[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks for the help in advance!