Nested IF/INDEX/COLLECT
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)
Best Answer
-
You would use something along the lines of
=original_formula * IF(Status@row = "Seat Sale", [Total Students]@row, 1)
Answers
-
You would use something along the lines of
=original_formula * IF(Status@row = "Seat Sale", [Total Students]@row, 1)
-
Excellent - thank you very much!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66K Get Help
- 429 Global Discussions
- 149 Industry Talk
- 488 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 153 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!