Complex Logic Statements
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
Best Answer

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.
Answers

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.

THANK YOU so much!
Help Article Resources
Categories
Check out the Formula Handbook template!