increments2

[pytelium]

I have values in cells f5 and h5.These values range from 1000,but are in
bands, and there only allowed increments in each band.

Min max increment
1.00 2.00 0.01
2.02 3.00 0.02
3.05 4.00 0.05
4.10 6.00 0.10
6.20 10.00 0.20
10.50 20.00 0.50
21.00 30.00 1.00
32.00 50.00 2.00
55.00 100.00 5.00
110.00 1000.00 10.00

Thus allowed values are 1.01,1.02,-----,2.02,2.04-----,3.05,3.10,3.15
etc.


In cell c25 I have difference between f5 and h5.I need to put in cell
d25,the number of increments between f5 and h5.

Suppose f5 is 5.7 and h5 is 17. How many increments is this?

By hand,5.7 to 6 is 3 increments,6 to 10 is 20 increments and 10 to 17
is 14 increments, a total of 37 increments.

 

[JMB]

try this formula

=(SUMPRODUCT(--(B2:B11>=F5),--(A2:A11<=H5),((B2:B11-A2:A11)/C2:C11)))-((F5-VLOOKUP(F5,A2:C11,1,TRUE))/VLOOKUP(F5,A2:C11,3,TRUE)+(VLOOKUP(H5,A2:C11,2,TRUE)-H5)/VLOOKUP(H5,A2:C11,3,TRUE))

When I tested, I had the table in cells A1:C11 (Min in Col A, Max in Col B,
Increment in Col C) so you'll need to change these ranges to reflect where
your table is located.

Also, its an array formula, so you'll need to confirm w/Control+Shift+Enter
instead of just hitting Enter.

 

[JMB]

Correction:

=(SUMPRODUCT(--(B2:B11>F5),--(A2:A11<=H5),((B2:B11-A2:A11)/C2:C11)))-((F5-VLOOKUP(F5,A2:C11,1,TRUE))/VLOOKUP(F5,A2:C11,3,TRUE)+(VLOOKUP(H5,A2:C11,2,TRUE)-H5)/VLOOKUP(H5,A2:C11,3,TRUE))

I removed the ">=" in the first comparison. Also, I think you will need to
change your table (if it does not create a problem for you) to:

Min max increment
1.00 2.00 0.01
2.00 3.00 0.02
3.00 4.00 0.05
4.00 6.00 0.10
6.00 10.00 0.20
10.00 20.00 0.50
20.00 30.00 1.00
30.00 50.00 2.00
50.00 100.00 5.00
100.00 1000.00 10.00

 

[pytelium]

Hi JMB

I have entered the following formula into the sheet,but it tells m
there is an error in the formula,I dont know where.

My table is in d25:f34 instead of a1:c11 as in your last post. There i
data in f6 and h6,f7 and h7 etc.,so I will be using autofill. The tabl
will be unchanged,hence the absolute cell references.

Thanks

=(SUMPRODUCT(--($E$26:$E$34>F5),--($d$26:$d$3
<=H5),(($e$26:$e$34-$d$26:$d$34)/$F$26:$F$34)))-((F5
(F5,$D$26:$F$34,1,TRUE))/VLOOKUP(F5,$D$26:$F$34,3,TRUE)+(VLOOKUP(H5,$D$26:$F$34,2,TRUE)-(H5,$D$26:$F$34,3,TRUE)

 

[JMB]

When you compare the two formulas carefully, yours is missing 2 VLOOKUP
statements. Also, the last snippet is not the same. There is a "(" that s/b
")" and on the other side of H5 reference. Also, missing "/" sign.

(VLOOKUP(H5,$D$26:$F$34,2,TRUE)-(H5,$D$26:$F$34,3,TRUE))

(VLOOKUP(H5,D26:F34,2,TRUE)-H5)/VLOOKUP(H5,D26:F34,3,TRUE))

 

[pytelium]

Thanks JMB,success at last.

 

[JMB]

Most welcome.