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.


--
pytelium
------------------------------------------------------------------------
pytelium's Profile: http://www.excelforum.com/member.php...o&userid=25521
View this thread: http://www.excelforum.com/showthread...hreadid=476928

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,TRU E)-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.


"pytelium" wrote:


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.


--
pytelium
------------------------------------------------------------------------
pytelium's Profile: http://www.excelforum.com/member.php...o&userid=25521
View this thread: http://www.excelforum.com/showthread...hreadid=476928

Correction:

=(SUMPRODUCT(--(B2:B11F5),--(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,TRU E)-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" wrote:


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.


--
pytelium
------------------------------------------------------------------------
pytelium's Profile: http://www.excelforum.com/member.php...o&userid=25521
View this thread: http://www.excelforum.com/showthread...hreadid=476928

Hi JMB

I have entered the following formula into the sheet,but it tells me
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 is
data in f6 and h6,f7 and h7 etc.,so I will be using autofill. The table
will be unchanged,hence the absolute cell references.

Thanks

=(SUMPRODUCT(--($E$26:$E$34F5),--($d$26:$d$34
<=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))


--
pytelium
------------------------------------------------------------------------
pytelium's Profile: http://www.excelforum.com/member.php...o&userid=25521
View this thread: http://www.excelforum.com/showthread...hreadid=476928

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" wrote:


Hi JMB

I have entered the following formula into the sheet,but it tells me
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 is
data in f6 and h6,f7 and h7 etc.,so I will be using autofill. The table
will be unchanged,hence the absolute cell references.

Thanks

=(SUMPRODUCT(--($E$26:$E$34F5),--($d$26:$d$34
<=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))


--
pytelium
------------------------------------------------------------------------
pytelium's Profile: http://www.excelforum.com/member.php...o&userid=25521
View this thread: http://www.excelforum.com/showthread...hreadid=476928

Thanks JMB,success at last.


--
pytelium
------------------------------------------------------------------------
pytelium's Profile: http://www.excelforum.com/member.php...o&userid=25521
View this thread: http://www.excelforum.com/showthread...hreadid=476928

Most welcome.

"pytelium" wrote:


Thanks JMB,success at last.


--
pytelium
------------------------------------------------------------------------
pytelium's Profile: http://www.excelforum.com/member.php...o&userid=25521
View this thread: http://www.excelforum.com/showthread...hreadid=476928