How do I create a specific formula?

Elizabeth Smeller

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!

Kelly Moore

Hi @Elizabeth Smeller

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

Kelly Moore

Hi @Elizabeth Smeller

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

Elizabeth Smeller

That worked, thank you so much!