Complex Logic Statements

Kstalder

It might not even be possible, but I need to know how to have a cell be populated with a value based on multiple criteria. Scenario - every audit costs a different amount, if it is 2 day or 3 day, remote or onsite, etc. I would like the line item to be able to populate based on column B = number of days and column C=remote or onsite to end in column D with a value. There would have to be a "key" somewhere for the different combinations. Is that even a thing?

two day + onsite returns value of $15,000.00 

three day + onsite returns a value of $18,000.00 

two day + remote returns a value of $11,500.00 

three day + remote returns a value of $14,500.00 

Danielle Arteaga

Assuming your set up looks something like this:

Place this formula in the [Audit Cost] column:

=IF(AND([Length of Audit]@row = 2, [Audit Location]@row = "Onsite"), 15000, IF(AND([Length of Audit]@row = 3, [Audit Location]@row = "Onsite"), 18000, IF(AND([Length of Audit]@row = 2, [Audit Location]@row = "Remote"), 11500, IF(AND([Length of Audit]@row = 3, [Audit Location]@row = "Remote"), 14500, ""))))

(Just change the names of the columns in [] above to match your columns containing the audit location and audit durations)

If you have other combinations (different durations or locations), you can repeat the pattern of this nested IF formula to include them.

ALSO - format the [Audit Cost] column for currency, so it automatically adds the $, the comma and decimal points.