Find amount of range that's within specific sub ranges

[d_tech]

Let's say I have random numbers from 0.00 to 2.50 in columns H and I indicating a range.

For each row, column J is supposed to tell me how much of that is within certain specific ranges of between 0.00 to 0.29, 0.79 to 1.29, and 1.79 to 2.29

For example H1 is 0.25 and I1 is 2.00 then J1 is supposed to show 0.75

How could that be done?

 

[awoohaw]

how is 2.00 *.25 = .75. Can you explain the arithmetic please?
I don't understand your range gaps you skip .30 to .78, 1.30 to 1.78.
can you give more examples?

 

[d_tech]

how is 2.00 *.25 = .75. Can you explain the arithmetic please?
I don't understand your range gaps you skip .30 to .78, 1.30 to 1.78.
can you give more examples?

Okay for example there'd be .30 in column H and .80 in column I then column J should show 0.01

Column H to column I is a range but column J is supposed to show how much of that would go into specific sub ranges

 

[awoohaw]

still don't understand, sorry. can you post an xl2bb mini worksheet?
with just hand entered expected outcomes.

is there multiplication? or is it comparison? .8 * .3 is not .01... it is .24.

 

[kevin9999]

I understand what you want, but I'm not capable of producing an elegant solution. Hopefully, someone cleverer than me (most people here) will provide a better solution...

Book1
	G	H	I	J
1		0.25	2.00	0.75
2
3	range 1	0.00	0.29
4	range 2	0.79	1.29
5	range 3	1.79	2.29
Sheet1

Cell Formulas
Range	Formula
J1	J1	=(MIN($I$1,I3))-MAX($H$1,H3)+(MIN($I$1,I4))-MAX($H$1,H4)+(MIN($I$1,I5))-MAX($H$1,H5)

 

[d_tech]

I understand what you want, but I'm not capable of producing an elegant solution. Hopefully, someone cleverer than me (most people here) will provide a better solution...

Book1
	G	H	I	J
1		0.25	2.00	0.75
2
3	range 1	0.00	0.29
4	range 2	0.79	1.29
5	range 3	1.79	2.29
Sheet1

Cell Formulas
Range	Formula
J1	J1	=(MIN($I$1,I3))-MAX($H$1,H3)+(MIN($I$1,I4))-MAX($H$1,H4)+(MIN($I$1,I5))-MAX($H$1,H5)

Thanks, this helped me figure it out. In itself it doesn't quite work because it needs to disregard the negatives; but I got something for now that works. I'll attach the mini-sheet

Book1.xlsx
	H	I	J	K	L	M	N	O	P
1	0.25	2.00	0.04	0.50	0.21	0.75		0.00	0.29
2	0.30	0.80	-0.01	0.01	-0.99	0.01		0.79	1.29
3	0.75	2.00	-0.46	0.50	0.21	0.71		1.79	2.29
Sheet1

Cell Formulas
Range	Formula
J1:J3	J1	=(MIN(I1,$P$1))-MAX(H1,$O$1)
K1:K3	K1	=(MIN(I1,$P$2))-MAX(H1,$O$2)
L1:L3	L1	=(MIN(I1,$P$3))-MAX(H1,$O$3)
M1:M3	M1	=SUMIF(J1:L1,">0",J1:L1)