I don't follow your point here:
Excel Formula:
=IF(AND(ISBLANK(R6),ISBLANK(S6))*(Q6<>0),1,0)
formula looks into the columns containing prices from two direct order publishers (Cengage and Pearson) and two local suppliers (offering prices for all other publishers).
That formula looks at values on the same row in columns R,S, and Q. The first two (R and S) are associated with the Direct Order publishers (incidentally, you mentioned these column contain "prices", but the table doesn't say anything about price...only a number...you might consider changing the column titles to say something about "Price"). The last variable in the equation is found in column Q, and represents a quantity needed to "Order", and that depends on columns N, O, and P. From what I can tell, none of these references look at the columns associated with the two local suppliers (columns T and U). Am I missing something?
Incidentally, this formula (if it really is supposed to look only at R,S, and Q) can be trimmed down to this:
Excel Formula:
=--AND(ISBLANK(R4:S4),Q4<>0)
...to deliver the same result. But I question whether this formula is what you really want. My understanding is that you want to proceed like this:
- For a variety of different textbooks shown in each row in column J, determine the number of additional copies of the textbook that should be ordered (column Q) based on the quantity of textbooks needed by students and instructor (sum of columns N and O) compared to the quantity of textbooks currently in stock (column P).
- For cases where the number of textbooks to Order (column Q) is >0, the Publisher (col. L) needs to be considered, as books from Cengage and Pearson need to be ordered exclusively and directly from those publishers. If this is true, then why would the Quote formula for a Pearson book require that the Price fields for both Cengage and Pearson be blank? Is it because you don't have a price from the relevant publisher yet...and that is why you are doing this step? I don't understand what you are trying to accomplish here. For a Pearson book, you would expect a price to be shown for Pearson (under col. S), but the formula used will automatically generate a 0 under the Quote column...is that intended? Are you using the Quote column to flag which rows have no price upon which to make a purchasing decision?
To me, it looks as if you want to determine the lowest price available for a book in column X (is that correct?). Are there ever any cases when either Cengage or Pearson offer prices on a book whose publisher is someone else (e.g. might McGraw-Hill hold publishing rights for a hardcover version of a book and Pearson offer the softcover version...in which case you might have prices for both?). Rather than make the low price determination dependent on whether Order<>0 (col. Q), have you considered using a filter to evaluate all prices available, regardless of what quantity is in the Order column, except make the formula more direct? By "more direct", I mean to explicitly require that Pearson books use the Pearson price, Cengage books use the Cengage price, and for any other publishers' books, the lowest offering from Supplier1 and Supplier2 is taken. This is reflected in a new formula in col. X.
I wasn't able to sort the Listing sheet because the entire sheet contains all the departments ordered by years, and after each year I'd have a row where I would calculate the price for that student package (column X Price final) and the total cost for that year (column AA COST). Then I would have an extra row for that department, adding up all the total costs for all years within that department. That meant that no sorting could take place.
Generally, I try to keep the foundational data together, organized in such a way to make it easy to sort, filter, extract, etc. If you do this, then anything that is derivative (such as subtotals by year, publisher, etc.) can be easily done with formulas, pivot tables, etc. elsewhere, but not in the foundational data set. Inserting those derivative rows directly into the data table is often done, it's intuitive and seems to make good sense, but doing so often causes problems with subsequent analysis. You may be able to extract the information you want using a formula that excludes those extra rows, provided those rows contain something in common (perhaps the words "yearly total"?) that would allow them to be filtered out.
How do I adapt the UNIQUE FILTER formula so that it only prints Cenage or Pearson to their respective sheets if the result in QUOTE column is zero?
You need to multiply all of the filtering conditional arrays together since they represent a logical AND condition. In other words, you want Publisher="CENGAGE" AND Quote<>0 (note this last expression is the opposite of what you tried...you want to consider entries where the Quote value is not 0, and exclude those entries where Quote=0), so...
(Listing!L:L="CENGAGE")*(Listing!V:V<>0), but I wouldn't recommend doing this as a full column operation. Instead, choose some reasonable size for your range references. A lot has been written about this practice...for example, see:
Should you use Whole Column References in Excel? While there are some advantages, the disadvantages make it risky. Find out why.
www.excelcampus.com
...not print anything that has a 0 in that column
...so that it only prints Cenage or Pearson to their respective sheets if the result in QUOTE column is zero
Once again, you are sending a confusing message. The Quote formula in column V appears to be intended for cases where you need pricing information from a vendor, and in those cases, the formula in col. V shows a 1 (and since the col. V formula returns only 0 or 1, the equivalent filter would either say Quote=1 or Quote<>0). In the small example below, I used Data Validation to create a cell (L12) that allows for the selection of either Cengage, Pearson, or Other. The Filter formula in J15 spill results from the main table to show the relevant entries that have a Quote (col. V) value of 1.
| Book5 (version 1)_20231208.xlsx |
|---|
|
|---|
| J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB |
|---|
| 1 | | | | | | | | | FOREIGN | SUPPLIERS | LOCAL | SUPPLIERS | | | | | | | |
|---|
| 2 | | | | | | | | | DIRECT | ORDER | | | | | | | | | |
|---|
| 3 | Textbook | Author | Publisher | ISBN | Quantity | Additional | In stock | ORDER | CENGAGE | PEARSON | SUPPLIER1 | SUPPLIER2 | QUOTE | Supplier Final | Price Final | Availability | Tracking | COST | ARRIVED # |
|---|
| 4 | Computer Organization and Architecture | a | McGraw-Hill | | 5 | 1 | 6 | 0 | | | 50 | 50 | 0 | SUPPLIER1, SUPPLIER2 | 50 | | | 0 | |
|---|
| 5 | Programming and Problem Solving II | b | Pearson | | 5 | 1 | 0 | 6 | | 50 | 50 | | 0 | Pearson | 50 | | | 300 | |
|---|
| 6 | Programming and Problem Solving II | b | Pearson | | 5 | 1 | 0 | 6 | | | | | 1 | Pearson | no price | | | no price | |
|---|
| 7 | Discrete Mathematics | c | CENGAGE | | 5 | 1 | 0 | 6 | 60 | | 50 | | 0 | Cengage | 60 | | | 360 | |
|---|
| 8 | Discrete Mathematics | d | CENGAGE | | 5 | 1 | 0 | 6 | | | | | 1 | Cengage | no price | | | no price | |
|---|
| 9 | Discrete Mathematics | e | Wiley | | 5 | 1 | 0 | 6 | | | | | 1 | | no price | | | no price | |
|---|
| 10 | | | | | | | | | | | | | | | | | | | |
|---|
| 11 | | | | | | | | | | | | | | | | | | | |
|---|
| 12 | Choose PUBLISHER at right | | Other | | | | | | | | | | | | | | | | |
|---|
| 13 | | | | | | | | | | | | | | | | | | | |
|---|
| 14 | Textbook | Author | Publisher | ISBN | Quantity | Additional | In stock | ORDER | | | | | | | | | | | |
|---|
| 15 | Discrete Mathematics | e | Wiley | 0 | 5 | 1 | 0 | 6 | | | | | | | | | | | |
|---|
|
|---|
