Nested IF/INDEX/COLLECT

HHansford

Hello,

I’m looking to add to an existing formula. The formula below checks if the Country and Instructor Currency match and pulls in the Fee if it does match, or the Converted Fee if it doesn’t match. The formula also matches the Instructor Name, Course and Format match.

This has been working great where I have an instructor with a set fee for a class. However, some instructors are paid on a per student basis. Their fee needs to be multiplied by the number of students in the class. Currently the formula pulls in the fee for 1 student only.

I know which instructors fall into this category because the Status column will say Seat Sale. I also have a Total Students column so I know how many students are enrolled.

Anythoughts on how to add this in? If it another IF Statement?

=IFERROR(INDEX(COLLECT(IF(OR(Country@row =

"Combo", [Instructor Currency]@row = Country@row), {Fee_Instructor

Fee}, {Converted Fee_Instructor Fees}), {Instructor

Name_Instructor Fees}, Instructor@row,

{Course_Instructor Fees}, Course@row,

{Format_Instructor Fees}, Format@row), 1), 0)

Paul Newcome

You would use something along the lines of

=original_formula * IF(Status@row = "Seat Sale", [Total Students]@row, 1)

Paul Newcome

You would use something along the lines of

=original_formula * IF(Status@row = "Seat Sale", [Total Students]@row, 1)

HHansford

@Paul Newcome

Excellent - thank you very much!

Paul Newcome

Happy to help. 👍️