increments2

P

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

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

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
 
P

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

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

variable gaps 1
increments 5
linking multiple spreadsheets 0
Graphing Database Growth Rate 1

Top