increments2

**pytelium** · 10-17-2005, 03:43 PM

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 · 10-17-2005, 10:05 PM

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.

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

JMB · 10-17-2005, 10:05 PM

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

**pytelium** · 10-18-2005, 02:19 PM

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$34>F5),--($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))

JMB · 10-18-2005, 10:05 PM

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$34>F5),--($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
>
>

**pytelium** · 10-19-2005, 06:30 PM

Thanks JMB,success at last.

JMB · 10-19-2005, 08:05 PM

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
>
>