How do I create a specific formula?
Hi, please refer to the picture below. Is there a formula that can pre-populate the 'Hours' columns based on the selections made in the 'Type' and 'Size' columns? For example, if I select 'SOW' and 'Medium' in the 'Type' and 'Size' columns, then I want the formula to automatically populate '320' in all 3 columns (1. Est. IM Hours 2. Est. PM Hours 3. Est. BA Hours). Thanks!
Best Answer
-
Yes to your question, however each of the three columns will need it's own formula.
We will use a nested IF formula, which means we will have a series of IF statements together in one formula.
Because you have multiple criteria that must happen together, we will use a special nested IF called the IF(AND). This allows us to group all the simultaneous conditions that must happen together for the IF to be a true statement. The IF(AND) has the syntax of IF(AND(condition1, condition2, condition3, etc),then whatever happens with true, else [whatever happens with false, if anything]). Note how a parenthesis closed the AND function to indicate the final criteria in the simultaneous conditions, or said a different way, parentheses enclose the AND group before we move on to the THEN part of the IF statement.
Put this formula in your [Est. IM Hours] column
=IF(AND(Type@row="SOW", [Size (S, M, L)]@row="Medium"), 320, IF(AND(Type@row="CC", [Size (S, M, L)]@row="Small"), 14, IF(AND(Type@row="CC", [Size (S, M, L)]@row="Medium"), 21, IF(AND(Type@row="CC", [Size (S, M, L)]@row="Large"), 42))))
For the other two columns - if the IF(AND) varies from this first column, then double click in the [Est. IM Hours] column on the same row as your cursor to highlight this entire formula, and paste this entire formula into each of your other columns. You can then edit the hours or the words as needed in the other columns.
If the other two columns will always equal the [Est. IM Hours] column, then in each of the two columns add
=[Est. IM Hours]@row
To make your formulas robust, right click in each column and choose Convert to Column Formula at the very bottom of the column menu list.
You can find more information on the IF(AND) function here
If you want more information on column formulas, you can find it here. Be sure to note how to edit these type of formulas once they are designated a column formula.
cheers,
Kelly
Answers
-
Yes to your question, however each of the three columns will need it's own formula.
We will use a nested IF formula, which means we will have a series of IF statements together in one formula.
Because you have multiple criteria that must happen together, we will use a special nested IF called the IF(AND). This allows us to group all the simultaneous conditions that must happen together for the IF to be a true statement. The IF(AND) has the syntax of IF(AND(condition1, condition2, condition3, etc),then whatever happens with true, else [whatever happens with false, if anything]). Note how a parenthesis closed the AND function to indicate the final criteria in the simultaneous conditions, or said a different way, parentheses enclose the AND group before we move on to the THEN part of the IF statement.
Put this formula in your [Est. IM Hours] column
=IF(AND(Type@row="SOW", [Size (S, M, L)]@row="Medium"), 320, IF(AND(Type@row="CC", [Size (S, M, L)]@row="Small"), 14, IF(AND(Type@row="CC", [Size (S, M, L)]@row="Medium"), 21, IF(AND(Type@row="CC", [Size (S, M, L)]@row="Large"), 42))))
For the other two columns - if the IF(AND) varies from this first column, then double click in the [Est. IM Hours] column on the same row as your cursor to highlight this entire formula, and paste this entire formula into each of your other columns. You can then edit the hours or the words as needed in the other columns.
If the other two columns will always equal the [Est. IM Hours] column, then in each of the two columns add
=[Est. IM Hours]@row
To make your formulas robust, right click in each column and choose Convert to Column Formula at the very bottom of the column menu list.
You can find more information on the IF(AND) function here
If you want more information on column formulas, you can find it here. Be sure to note how to edit these type of formulas once they are designated a column formula.
cheers,
Kelly
-
That worked, thank you so much!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.4K Get Help
- 364 Global Discussions
- 201 Industry Talk
- 430 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 446 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!