Help With Complicated Formula

Hi everyone,

I've written out an Excel 2013 spreadsheet detailing my book collection. It has over 550 entries, with column headings such as Book Name, Author, Year Released, Pages, Genre, If I've Read Them and also listing the Last 20 Books I've Read.



I've been learning how to Develop And Use Complex Spreadsheets at a Cert IV level and I have it putting out some interesting data by writing some cool formulas.

This added sheet has a set of formulas that allows me to type in the Author I'm wishing to search for in C2. I have customised it so automatically generates a list of ALL the Books, Year Released, Pages, etc... and added other formulas to express cool statistical data.



These formula that lists the books works great and I know enough of how it works to modify it even further to create additional pages that search for Genre and Series, listing the same results.

This is it for C13 (searching for Book Title):

FORMULA 1
{=IFERROR(INDEX('Book Listing'!$A$4:$A$554,SMALL(IF($C$2='Book Listing'!$B$4:$B$554,ROW('Book Listing'!$A$4:$A$554)-ROW('Book Listing'!$A$4)+1),ROW(1:1))),"-")}

Now, I am comfortable with this and it works like a dream. But now I am wanting to create a formula that's a little bit more complicated.

I want to create a new page that will modify this formula to look for a range of page numbers (eg, 0-100, 100-200, 300-400 so on). I want this page to use FORMULA 1 to list all my books within this range of pages.

I've managed to adapt a COUNTIF formula that finds for me the number of books within the range. Again, I have customised it to find others counts in the range.



FORMULA 2
=COUNTIF('Book Listing'!$D$4:$D$554,">0")-COUNTIF('Book Listing'!$D$4:$D$554,">100")

But I am not able to combine it with FORMULA 1 as this will only find the count, and not the list books as I want them.

If any person out there can help me adapt this FORMULA to find the range of numbers, I would be extremely grateful. I have been work shopping this and reading on numerous forums for some time and none can really enlighten me on how to modify it to search only a range of numbers.

Many thanks in advance,

Rick.

Try

=IFERROR(INDEX('Book Listing'!$A$4:$A$554,SMALL(
IF((VALUE(LEFT($C$2,FIND("-",$C$2)-1))<='Book Listing'!$D$4:$D$554)*(VALUE(MID($C$2,FIND("-",$C$2)+1,99))>='Book Listing'!$D$4:$D$554)*('Book Listing'!$D$4:$D$554<>""),ROW('Book Listing'!$A$4:$A$554)-ROW('Book Listing'!$A$4)+1),
ROW(1:1))),"-")

That's done the trick! Very impressive work there, I am very grateful.

Now I need to study the components, break them down and see how each of them work. While I am getting a good understanding of Excel formulas, it was uncharted territory 3 months ago and there is much for me to learn.

But again: many thanks!

Interesting. My IFERROR function does not automatically reply with "-" anymore...

Really? I got that on the 0-n range, so I added a test for <>"" in the IF.

Ahh, sorry. It's all good, my bad.

You must have a lot of knowledge on spreadsheets to create something like this. I tested the formula and it will work in any range (not just eg. 0-100). But it must be a range, not a single number. You're inserting Functions like VALUE, MID and FIND which I have no experience with.

I'm going to have to hit the books/forums for a few weeks before I can figure out how it works.

If you want to be able to input a range OR an exact page count, use

=IFERROR(INDEX('Book Listing'!$A$4:$A$55,
SMALL(IF((VALUE(LEFT($C$2,FIND("-",$C$2&"-")-1))<='Book Listing'!$D$4:$D$55)
*(VALUE(IF(ISNUMBER(FIND("-",$C$2)),MID($C$2,FIND("-",$C$2)+1,99),$C$2))>='Book Listing'!$D$4:$D$55)
*('Book Listing'!$D$4:$D$55<>""),
ROW('Book Listing'!$A$4:$A$55)-ROW('Book Listing'!$A$4)+1),
ROW(1:1))),"-")

Dude, that's really mind blowing stuff.

Here is another small adjustment. You can input a range, 201-300, a page count, 250, or a threshold, <=250 or >=300 (you must use <= not <)

=IFERROR(INDEX('Book Listing'!$A$4:$A$55,
SMALL(IF(LEFT($C$2,2)="<=",IF(('Book Listing'!$D$4:$D$55<=VALUE(MID($C$2,3,99)))*('Book Listing'!$D$4:$D$55<>""),ROW('Book Listing'!$A$4:$A$55)-ROW('Book Listing'!$A$4)+1),
IF(LEFT($C$2,2)=">=",IF('Book Listing'!$D$4:$D$55>=VALUE(MID($C$2,3,99)),ROW('Book Listing'!$A$4:$A$55)-ROW('Book Listing'!$A$4)+1),
IF((VALUE(LEFT($C$2,FIND("-",$C$2&"-")-1))<='Book Listing'!$D$4:$D$55)
*(VALUE(IF(ISNUMBER(FIND("-",$C$2)),MID($C$2,FIND("-",$C$2)+1,99),$C$2))>='Book Listing'!$D$4:$D$55)
*('Book Listing'!$D$4:$D$55<>""),
ROW('Book Listing'!$A$4:$A$55)-ROW('Book Listing'!$A$4)+1))),
ROW(1:1))),"-")

Bob, can the formula be adapted to search the array for part of a text? Say if I had typed in Fantasy in C2 and I wanted it also to return results for Science Fantasy or Dark Fantasy.