I'm working on what to me is a complicated workbook with many formulas that depend on different criteria. I found that I will have to break down my questions and focus on one thing at a time.
Below is the table; it shows two columns which contain two direct order publishers, and two columns with local suppliers that compete for publishers that are not direct order:
How do I combine the two MIN IF and MAX IF functions so I don't have to use a separate MIN and MAX every time a criteria changes? Both formulas look into column M to determine if the quantity is more than 0, then in the ranges for direct orders (MAX because only one publisher can offer the price for their book) or local suppliers (MIN because the two suppliers can offer different prices for the same book). I need the result to be the MAX value if there's a price in columns N or O and blanks in columns P and Q, and the MIN value if there are blanks in columns N or O and prices in columns P and Q. Can this be done?
Below is the table; it shows two columns which contain two direct order publishers, and two columns with local suppliers that compete for publishers that are not direct order:
| Book1 | ||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | |||
| 1 | FOREIGN | SUPPLIERS | LOCAL | SUPPLIERS | ||||||||||||||||||||||
| 2 | DIRECT | ORDER | ||||||||||||||||||||||||
| 3 | Year | Code | Title | Minor | Lecturer | Textbook | Author | Publisher | ISBN | Quantity | Instructor | In stock | TO ORDER | CENGAGE | PEARSON | SUPPLIER1 | SUPPLIER2 | QUOTE | Supplier Final | Price final | Availability | Tracking | COST | ARRIVED # | ||
| 4 | 1 | CS121 | Computer Organization and Architecture | Name Surname | Computer Organization and Architecture | McGraw-Hill | 5 | 1 | 0 | 6 | $ 40,00 | $ 50,00 | 1 | SUPPLIER1 | $ 40,00 | $ 240,00 | ||||||||||
| 5 | 1 | CS122 | Programming and Problem Solving II | Name Surname | Programming and Problem Solving II | Pearson | 5 | 1 | 0 | 6 | $ 50,00 | 0 | PEARSON | $ 50,00 | $ 300,00 | |||||||||||
| 6 | 1 | CS123 | Discrete Mathematics | Name Surname | Discrete Mathematics | CENGAGE | 5 | 1 | 0 | 6 | $ 60,00 | 0 | CENGAGE | $ 60,00 | $ 360,00 | |||||||||||
| 7 | 1 | CS124 | Discrete Mathematics | Name Surname | Discrete Mathematics | CENGAGE | 5 | 1 | 10 | 0 | $ 60,00 | 0 | #N/A | $ - | $ - | |||||||||||
| 8 | 1 | CS124 | Discrete Mathematics | Name Surname | Discrete Mathematics | Wiley | 5 | 1 | 0 | 6 | $ 50,00 | $ 40,00 | 1 | SUPPLIER2 | $ 40,00 | $ 240,00 | ||||||||||
Sheet2 | ||||||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| J4:J8 | J4 | =IF(F4="No textbook",0,(COUNTIFS('ORDER CALCULATIONS MINOR COLUMNS.xlsx'!Table1[Major],"Computer Science",'ORDER CALCULATIONS MINOR COLUMNS.xlsx'!Table1[Minor],"-",'ORDER CALCULATIONS MINOR COLUMNS.xlsx'!Table1[Year],"1"))) |
| K4:K8 | K4 | =IF(F4="No textbook",0,COUNTIF(E4,"<>")) |
| R4:R8 | R4 | =IF(AND(ISBLANK(N4), ISBLANK(O4)),1, 0) |
| S4,S8 | S4 | =INDEX(local_suppliers,MATCH(T4,P4:Q4,0)) |
| T4,T8 | T4 | =MIN(IF(M4<>0,P4:Q4)) |
| S5:S7 | S5 | =INDEX(direct_orders,MATCH(T5,N5:O5,0)) |
| T5:T7 | T5 | =MAX(IF(M5<>0,N5:O5)) |
| M4:M8 | M4 | =IF(J4+K4-L4<0,0,J4+K4-L4) |
| W4:W8 | W4 | =M4*T4 |
| Named Ranges | ||
|---|---|---|
| Name | Refers To | Cells |
| direct_orders | =Sheet2!$N$3:$O$3 | S5:S7 |
| local_suppliers | =Sheet2!$P$3:$Q$3 | S8, S4 |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| S1:S8 | Cell Value | contains "SUPPLIER2" | text | NO |
| S1:S8 | Cell Value | contains "SUPPLIER1" | text | NO |
| F1:F8 | Cell Value | contains "No textbook" | text | NO |
| P4:Q4 | Other Type | Color scale | NO | |
| J4:J8,M4:M8,W4:W8 | Cell Value | =0 | text | NO |
| S4:S8,H4:H8 | Cell Value | contains "CENGAGE" | text | NO |
| H4:H8,S4:S8 | Cell Value | contains "PEARSON" | text | NO |
| A4:O6,R4:X6,P5:Q6,A7:X8 | Expression | =$U4="Available" | text | NO |
| A4:O4,R4:X6,A5:Q6,A7:X8 | Expression | =$F4:$F28="" | text | NO |
| A4:O4,R4:X6,A5:Q6,A7:X8 | Expression | =$U4="Temporarily unavailable" | text | NO |
| A4:O4,R4:X6,A5:Q6,A7:X8 | Expression | =$U4="Print on demand" | text | NO |
| A4:O4,R4:X6,A5:Q6,A7:X8 | Expression | =$U4="Out of print" | text | NO |
| A4:O4,R4:X6,A5:Q6,A7:X8 | Expression | =$V4="Ordered" | text | NO |
| A4:O4,R4:X6,A5:Q6,A7:X8 | Expression | =$V4="Arrived" | text | NO |
| A4:O4,R4:X6,A5:Q6,A7:X8 | Expression | =$V4="Incomplete" | text | NO |
| A4:O4,R4:X6,A5:Q6,A7:X8 | Expression | =$V4="Late" | text | NO |
How do I combine the two MIN IF and MAX IF functions so I don't have to use a separate MIN and MAX every time a criteria changes? Both formulas look into column M to determine if the quantity is more than 0, then in the ranges for direct orders (MAX because only one publisher can offer the price for their book) or local suppliers (MIN because the two suppliers can offer different prices for the same book). I need the result to be the MAX value if there's a price in columns N or O and blanks in columns P and Q, and the MIN value if there are blanks in columns N or O and prices in columns P and Q. Can this be done?
