v Lookup series of data ranging + and - 100

[scottlarock]

Good day excel brothers, seniors & juniors

let's get str8 to the point.
My data sheet comprises of projects in MW. So I have the name of a project and there power in MW.
I would like to fetch all projects that have a similar MW power within a range of + and - 100, and align them one under another.
i.e.:

project 1 = 1000 MW; year=2007
project 2 = 1200 MW; year=2008
project 3 = 900 MW; year=2008
project 4 = 1100 MW;year=2009
project 5 = 800 MW;year=2009

I need a formula that gives me fetches all projects that are =1000 or + and - 100 MW
the result should be the following :

project 1 = 1000 MW; year=2007
project 3 = 900 MW; year=2008
project 4 = 1100 MW;year=2009

I hope I made myself clear...in any case...I'll pass the torch to the Master that can work this dilemna out.
Tons of thanks in advance !!

 

[scottlarock]

Hi domenic,
I have integrated the formulas to my tool and they work great !
I would now like to do the exact same thing, but only get the last 15 projects from the database.
Because my database is big, and I don't need to do the analysis on every single datapoint.
I have classified the projects by date, oldest ones on top, and most recent ones on the bottom.
Mabe you have a trick to choose either to get the 15 most recent ones corresponding to the search criteria (900 MW to 1100 MW) or the ones ranging in years 2008 & 2009 only.
Hoping to hear from you soon.
Enjoy your Sunday !
and a happy easter all the way from Zurich :°)

 

[Domenic]

Here are two possibilities. They're basically the same thing. The only difference is that the second one uses a helper column, which should be a bit more efficient.

[Option 1]

Let A2 contain 15, indicating that you want the last 15 records that meet the criteria.

Let B2 contain 1000, indicating the criteria that needs to be met, +/-100

C2, confirmed with CONTROL+SHIFT+ENTER, copied across and down:

=IF(ROWS(C$2:C2)<=$A$2,INDEX('Sheet 2'!A$2:A$100,SMALL(IF(ROW('Sheet 2'!$B$2:$B$100)>=LARGE(IF(ABS('Sheet 2'!$B$2:$B$100-$B$2)<=100,ROW('Sheet 2'!$B$2:$B$100)),$A$2),IF(ABS('Sheet 2'!$B$2:$B$100-$B$2)<=100,ROW('Sheet 2'!$B$2:$B$100)-ROW('Sheet 2'!$B$2)+1)),ROWS(C$2:C2))),"")

[Option 2]

Let A2 contain 15, indicating that you want the last 15 records that meet the criteria.

Let B2 contain 1000, indicating the criteria that needs to be met, +/-100

C2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IF(ROWS(C$2:C2)<=$A$2,SMALL(IF(ROW('Sheet 2'!$B$2:$B$100)>=LARGE(IF(ABS('Sheet 2'!$B$2:$B$100-$B$2)<=100,ROW('Sheet 2'!$B$2:$B$100)),$A$2),IF(ABS('Sheet 2'!$B$2:$B$100-$B$2)<=100,ROW('Sheet 2'!$B$2:$B$100)-ROW('Sheet 2'!$B$2)+1)),ROWS(C$2:C2)),"")

D2, copied across and down:

=IF($C2<>"",INDEX('Sheet 2'!A$2:A$100,$C2),"")

Adjust the ranges, accordingly.

 

[scottlarock]

Hey Domenic,

The formulas don't work on my spreadsheets.
I am still using the same example, that is to say :

'Sheet 2'!A2:A100 = Project names i.e. : Project 1 through 99
'Sheet 2'!B2:B100 = Power in MW i.e. : 1000
'Sheet 2'!C2:C100 = Project Year i.e. : 2009

'Sheet 1'!A1 = # of desired reference projects
'Sheet 1'!A2 = 15
'Sheet 1'!B1 = desired MW power 1000 (+/- 100)
'Sheet 1'!B2 = 1000

'Sheet 1'!C2 =IF(ROWS(C$2:C2)<=$A$2,INDEX('Sheet 2'!A$2:A$100,SMALL(IF(ROW('Sheet 2'!$B$2:$B$100)>=LARGE(IF(ABS('Sheet 2'!$B$2:$B$100-$B$2)<=100,ROW('Sheet 2'!$B$2:$B$100)),$A$2),IF(ABS('Sheet 2'!$B$2:$B$100-$B$2)<=100,ROW('Sheet 2'!$B$2:$B$100)-ROW('Sheet 2'!$B$2)+1)),ROWS(C$2:C2))),"")

Excel indicates there is an error with the formulas for both options 1 & 2 and thus will not allow me to press enter with or without the confirm trick.
Do they work on your spreadsheets ?
Thanks a bunch..as usual :°)

 

[Domenic]

I am still using the same example...

That's good, it makes it easier to troubleshoot...

Excel indicates there is an error with the formulas for both options 1 & 2 and thus will not allow me to press enter with or without the confirm trick.

Does Excel indicate the type of error? I'm assuming that you've replaced the commas in the formula with semi-colons for your version of Excel, correct?

Do they work on your spreadsheets ?

Yes, they do...

 

[scottlarock]

Yep, you nailed it ! again...
It was the semi colons...The formulas work really well :°)
I think your 'option 2' may be a tiny bit more efficient as 'option 1' makes excel crash when dragging the formulas down all at once...but that's nothing..I just gotta take it easy on the machine...

Do you have a quick explanation for the reason why confirming these formulas make them work .. but the same formula not confirmed does not ?
Thanks a million Domenic !
Enjoy your evening

 

[Domenic]

Do you have a quick explanation for the reason why confirming these formulas make them work .. but the same formula not confirmed does not ?

Confirming with CONTROL+SHIFT+ENTER signifies to Excel that we're dealing with an array formula. For more information, have a look at Excel's help files under 'array formula'.

Thanks a million Domenic !

You're very welcome!

 

[Ketan]

I would like to suggest following steps :
1)Create a col " D" say " check column".
2)Insert a formula =IF(AND(B2>=900,B2<=1100),"data of my criteria"," ") considering the field "porject no." in A1,MW in B1,year in C1
3)Make a pivot table with filed "check column in "page" area of layout and select the "data of my criteria" from drop list. Project name,year either in row or in column as required and MW in data filed of layout.
4)Link the output of the pivot cell to the desired destination.

Check at your end and share the experience.

 

[scottlarock]

Here are two possibilities. They're basically the same thing. The only difference is that the second one uses a helper column, which should be a bit more efficient.

[Option 1]

Let A2 contain 15, indicating that you want the last 15 records that meet the criteria.

Let B2 contain 1000, indicating the criteria that needs to be met, +/-100

C2, confirmed with CONTROL+SHIFT+ENTER, copied across and down:

=IF(ROWS(C$2:C2)<=$A$2,INDEX('Sheet 2'!A$2:A$100,SMALL(IF(ROW('Sheet 2'!$B$2:$B$100)>=LARGE(IF(ABS('Sheet 2'!$B$2:$B$100-$B$2)<=100,ROW('Sheet 2'!$B$2:$B$100)),$A$2),IF(ABS('Sheet 2'!$B$2:$B$100-$B$2)<=100,ROW('Sheet 2'!$B$2:$B$100)-ROW('Sheet 2'!$B$2)+1)),ROWS(C$2:C2))),"")

[Option 2]

Let A2 contain 15, indicating that you want the last 15 records that meet the criteria.

Let B2 contain 1000, indicating the criteria that needs to be met, +/-100

C2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IF(ROWS(C$2:C2)<=$A$2,SMALL(IF(ROW('Sheet 2'!$B$2:$B$100)>=LARGE(IF(ABS('Sheet 2'!$B$2:$B$100-$B$2)<=100,ROW('Sheet 2'!$B$2:$B$100)),$A$2),IF(ABS('Sheet 2'!$B$2:$B$100-$B$2)<=100,ROW('Sheet 2'!$B$2:$B$100)-ROW('Sheet 2'!$B$2)+1)),ROWS(C$2:C2)),"")

D2, copied across and down:

=IF($C2<>"",INDEX('Sheet 2'!A$2:A$100,$C2),"")

Adjust the ranges, accordingly.

Hello Domenic I hope all is well for you.

Here is the deal, in the same example (option 1), what happens when A2 which is 15 is actually higher than the amount of corresponding projects available in the database ?

If I only have 10 corresponding projects, but A2=15, then I get a #NUM! error.

I would like to leave A2=15, so the formulas will fetch the last 15 projects OR LESS from the database, and not give me an error message if there aren't 15 projects projects, but rather a blank cell.

So if I have only 10 corresponding projects in the database, then even if A2=15, the formulas will give me the last 10 projects....instead of the last 15, and blank cells on the remaining 5 lines below the 10.

I hope I was clear enough in my explanations.

Tons of thanks in advance....hoping to hear from you soon.

 

[Domenic]

Replace 15 in A2 with the following...

=MIN(15,SUMPRODUCT(--(ABS('Sheet 2'!$B$2:$B$100-$B$2)<=100)))

 

[scottlarock]

You're the Best !
It works perfect..I don't know how you come up with such magic..incredible.
Have a nice day