Question

Use excel
Add a column called “sale type” which describes how profitable the sale was. It has to show one of the following values:
•   Good, if either the sale quantity is larger than 12, or the product unit price is more than $15, or the total profit is more than $90
•   Bad, if the product price is less than $10 and the quantity is less than 5, or if the total sale value is less than $80 and the payment is done by credit card.
•   Soso, otherwise
Write a formula for this column.

The Excel built-in data validation does not support all types of validation, but only the typical and basic ones. Sometimes our data validation involves more complicated schemes not found in Excel, in these cases we have to do data validation by writing formulas. Create a column called “Error” and set its color to red. For each sale show relevant error messages in this column if any of the rules are violated:
Rule 1: Only credit card transaction can have the “pending” status.
Rule 2: A sale with total value less than $50 cannot be done by credit card.
Rule 3: A sale with total value more than $500 cannot be done by cash (for security reasons!)

Answer 1

Lets say that column A has number of units starting from secodn row
column B has price per unit
column C has total price (total profit)
Column E has "CC" for credit card payments
then the following formula will label a cell Good, Bad, or soso

=IF(A2>12,"Good",IF(B2>15,"Good",IF(C2>90,"Good",IF(B2<10,IF(A2<5,"Bad",IF(C2<80,IF(E2="CC","Bad","soso"),"soso")),"soso"))))

For the same table if column F has Pending for Pending transactions, the following formula in new cell gives us Error or not based on the rules given:
=IF(E2<>"CC",IF(F2="Pending","Error",IF(C2>500,"Error","Ok")),IF(C2<50,"Error","Ok"))

we can then apply select filter on the column and set cell color to be red for text Error and cell color to be green for text ok