If Statement Help
Hi there,
It's been a while since I've used smartsheets and I"m dusting off some cobwebs.
I have a column in my data set that determines the Project Type of a topic (i.e Course, certification etc.) Based on the Project Type, we have an formula per type that helps us calculate an estimated timeline.
I'd like to create a column that has an formula that will provide an estimated # of hours based on the project type.
For Example: Project A is a Course that is expected to have 4 modules.
Our formula to provide an estimated timeframe is:
(# modules x 30 hours) + 120 hours (our logistics turn around) --> (4x30)+120= 240 hours of work estimated.
How can i string my IF statement together to gather this and run for all options (we have 4 course options)? Is there a better way to do this?
Thanks!
Sarah
Answers
-
So you would want to do something like:
=IF([Project type]@row="Project A", (4 * 30)+120,IF([Project type]@row="Project B", (5 * 30)+120,0)
So on and so forth.
Does that about do the trick?
-
You can build it out as a nested IF formula, that would look like
=(IF([Project Type]@row = "Course", 4, IF([Project Type]@row = "Certification", 10, IF([Project Type]@row = "XYZ", 20...)))*30)+120
with as many variables as you have. Another way is you could have a lookup table to reference and then use INDEX/MATCH which would be cleaner, especially if your variable list could grow.
=INDEX([Hours Range], MATCH([Project Type]@row, [Project Type Range], 0)*30+120
Jason Tarpinian - Sevan Technology
Smartsheet Aligned Partner
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 287 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!