Value within percentage range

[JS050]

Hi all,

I have been reading a lot of great answers on this forum already, but I can't seem to find the answer to the problem I am facing now.

In B2 I have the weight of wood in KG, for example. In B3 &B4 I have the range for which I would like the range to be (+10% & -10%). In D8:D17 is the range of wood in KG with the corresponding prices in E8:E17.

1. How do I get all the values within the + 10% & -10% in the cell I7 and below? Within the range of of D8:D17 there are some the same as the searching value and some are not the same. I have used =(INDEX($D$7:$D$17;MATCH($B$2;$D$7:$D$17;0);(ABS($B$2-$D$7:$D$17)<=ABS($B$2*$B$3))-(ABS($B$2-$D$7:$D$17)<=ABS($B$2*$B$4)))) and it only gives me 3700 as a returning value. Can somebody explain why?
2. How to I get the corresponding prices that go along with the wood in KG in J7 and down?

Thank you very much and much appreciated!

 

[AhoyNC]

Like this??

Book1
	A	B	C	D	E	F	G	H	I
1
2	Weight	3700
3	Procentual range	90%
4		110%
5				Weight	Price
6				3700	2000			Weight	Price
7				3000	1500			3700	2000
8				3240	1500			3700	2000
9				3240	1600			3450	1900
10				3240	1800			3700	2000
11				3700	2000			4000	2500
12				3450	1900
13				3700	2000
14				4000	2500
15				4300	1900
16				4200	2500
Sheet2

Cell Formulas
Range	Formula
H7:I11	H7	=FILTER(D6:E16,(D6:D16>=B2-(B2*0.1))*(D6:D16<=(B2*1.1)),"Out of Rnge")
Dynamic array formulas.

 

[JS050]

Like this??

Book1
	A	B	C	D	E	F	G	H	I
1
2	Weight	3700
3	Procentual range	90%
4		110%
5				Weight	Price
6				3700	2000			Weight	Price
7				3000	1500			3700	2000
8				3240	1500			3700	2000
9				3240	1600			3450	1900
10				3240	1800			3700	2000
11				3700	2000			4000	2500
12				3450	1900
13				3700	2000
14				4000	2500
15				4300	1900
16				4200	2500
Sheet2

Cell Formulas
Range	Formula
H7:I11	H7	=FILTER(D6:E16,(D6:D16>=B2-(B2*0.1))*(D6:D16<=(B2*1.1)),"Out of Rnge")
Dynamic array formulas.

Hi AhoyNC,

Thanks a lot for the great suggestion. Unfortunately, at work, we do not have the =filter function... Think we do not have the right Excel for that

Is there any other option you can think of?

In the meantime I have come up with the following: I have used =IF(AND($D$6:$D$16>=$B$2*$B$3;$D$6:$D$16<=$B$2*$B$4);$D$6:$D$16) in cells M6:M15 to only give the values within the 0,975 en 102,5 range. In cells N6:N15 the price is given accordingly with =IF.ERROR(VERT.ZOEKEN(M6;$D$6:$E$16;2;0);"ONWAAR"). (Onwaar = False)

But now I would like to have a summary of all the values, so not the false-values in D20: F27 like in the picture below. I have manually added the values, but what formula would accomplish this? I have tried to download the XL2BB, but unfortunately I can't get that to work..

Thanks again!

 

[AhoyNC]

Does this help. Should work with Excel 2010 and up.
Just drag formula down the row.

Book1
	A	B	C	D	E	F	G	H	I
1
2	Weight	3700
3	Procentual range	90%
4		110%
5				Weight	Price			Weight	Price
6				3700	2000			3700	2000
7				3000	1500			3700	2000
8				3240	1500			3450	1900
9				3240	1600			3700	2000
10				3240	1800			4000	2500
11				3700	2000
12				3450	1900
13				3700	2000
14				4000	2500
15				4300	1900
16				4200	2500
Sheet1

Cell Formulas
Range	Formula
H6:I10	H6	=IFERROR(INDEX($D$6:$E$16,AGGREGATE(15,6,(ROW($D$6:$D$16)-ROW($D$6)+1)/(($D$6:$D$16>=$B$2*$B$3)*($D$6:$D$16<=$B$2*$B$4)),ROWS($K$7:K7)),0),"")
H12:H13	H12	=IFERROR(INDEX($D$6:$E$16,AGGREGATE(15,6,(ROW($D$6:$D$16)-ROW($D$6)+1)/(($D$6:$D$16>=$B$2*$B$3)*($D$6:$D$16<=$B$2*$B$4)),ROWS($K$7:K12)),0),"")
B15:B16	B15	=IF(AND(D15>$F$1,D15<=$G$1),"T","")
Dynamic array formulas.

 

[AhoyNC]

Just use the formula in cell H6 above. It picked up some formulas I was just testing with in H12 and B15, that I didn't delete.

 

[JS050]

Just use the formula in cell H6 above. It picked up some formulas I was just testing with in H12 and B15, that I didn't delete.

Thank you very much for your swift reply!! Where is this part of the formula for : ,ROWS($K$7:K7)),0),"")?

 

[JS050]

Still don't know where this: ,ROWS($K$7:K7)),0),"") is for but the formula works!!! Thank you so much for your help!

 

[AhoyNC]

The ROWS($K$7:K7) is just a counter for the SMALL function. It doesn't really mater, but I meant for it to start in cell H6. I was working on different formulas and some got copied over into what I sent to you.

So, ROWS($H$6:H6) will give 1 in the first cell and then 2 in the next cell down. This just tells the SMALL function to pick the 1,2,3 etc. values that match.
The 0 in ROWS($K$7:K7)),0) tells the INDEX to look at all values in the column.
The "" is for the IFERROR function to return a blank cell if there is an error. As you keep dragging the formula down, once it runs out of values to return you would get a #NUM error.

Here is a cleaner copy.

Book1
	A	B	C	D	E	F	G	H	I
1
2	Weight	3700
3	Procentual range	90%
4		110%
5				Weight	Price			Weight	Price
6				3700	2000			3700	2000
7				3000	1500			3700	2000
8				3240	1500			3450	1900
9				3240	1600			3700	2000
10				3240	1800			4000	2500
11				3700	2000
12				3450	1900
13				3700	2000
14				4000	2500
15				4300	1900
16				4200	2500
Sheet1

Cell Formulas
Range	Formula
H6:I10	H6	=IFERROR(INDEX($D$6:$E$16,AGGREGATE(15,6,(ROW($D$6:$D$16)-ROW($D$6)+1)/(($D$6:$D$16>=$B$2*$B$3)*($D$6:$D$16<=$B$2*$B$4)),ROWS($H$6:H6)),0),"")
Dynamic array formulas.

 

[JS050]

Thanks a lot! Works like a charm!

 

[AhoyNC]

You're welcome. Thanks for the feedback.