Formula Help, conditional formatting/If statement

LMallery

I need a calculation to occur when criteria are met in another cell. For example, I want column D1 to reflect the following:

if column A1 contains "Yes", I want the sheet to multiply column C1 by .10.

If A1 contains, "No". I want either no action or a zero to be entered into D1.

Any guidance would be appreciated!

Genevieve P.

Hi @LMallery

Yes, an IF statement is the way to go, here!

For this statement:

if column A1 contains "Yes", I want the sheet to multiply column C1 by .10.

=IF(CONTAINS("Yes", [A1]@row), [C1]@row * 10

Then the next statement:

If A1 contains, "No". I want either no action or a zero to be entered into D1.

IF(CONTAINS("No", [A1]@row), 0

For a full statement of:

=IF(CONTAINS("Yes", [A1]@row), [C1]@row * 10, IF(CONTAINS("No", [A1]@row), 0, ""))

This is presuming that your A1 will have other text with it as well. If A1 is a column that will only ever have "Yes", "No", or Blank, then you can write this:

=IF([A1]@row = "Yes", [C1]@row * 10, "")

Let me know if this makes sense! If not, it would be helpful to see a full screen capture of your sheet (but block out sensitive data), and a list of all the statements you would want it to return.

Cheers,

Genevieve

LMallery

@Genevieve P. That did help-Thank you! Can you assist with one more?

Column "SVI Score" will contain either i, ii, or iii. Based on this entry, I want column "Monthly Bonus Amount" to be multiplied by a number and return a value in "SVI Bonus". See below:

i = .1

ii = .2

iii = .3

I tried to use the following but it only calcuates the first condition. It will not calculate correctly for "ii" or "iii" :

=IF(CONTAINS("i", [SVI Score]@row), [Monthly Bonus Amount]@row * .10, IF(CONTAINS("ii", [SVI Score]@row), [Monthly Bonus Amount]@row *.20, IF(CONTAINS("iii",[SVI Score]@row, [Monthly Bonus Amount]@row*.30))

You can see here that it multiplied by .1, not .3 even though SVI Score contains "iii".

Genevieve P.

Hi @LMallery

Thank you for the screen capture, this is very helpful!

If statements will stop as soon as they find a criteria that matches. This means that since "iii" contains "i" then your first statement will be true for all of the potential cell values.

Instead of CONTAINS, try going right for the criteria with = "i"

This means that the cell has to have that exact value, not just contain the value. I would also write them backwards, starting with "iii" so that there's no possibility of another match coming first.

Try this:

=IF([SVI Score]@row = "iii", [Monthly Bonus Amount]@row * .30, IF([SVI Score]@row = "ii", [Monthly Bonus Amount]@row * .20, IF([SVI Score]@row = "i", [Monthly Bonus Amount]@row * .10)))

Cheers!

Genevieve

LMallery

Great! This worked. Thank you