IF Statement Problem

[Florida1510]

Good morning, I have been struggling with an IF formula I’m hoping someone can assist me with:
Issue - I have three different billing options:

1. Yes – Midwest
2. Yes – NE/SW/CAN
3. No

What I need for my formula to do is to look at the above three options in cell “I2” if the result in the field = options 1 or 2 I need formula to then put in the number shown in cell G2 (note cell G2 is also a formula I’m not sure if that matters) in cell H2. If “I2” = Option 3 of “No” I need for cell H2 to be populated with a “0”.

For the life of me I can’t seem to get this formula to work properly. Any help would be greatly appreciated. Thanks in advance.

 

[Fluff]

What are the exact values in I2?

 

[etaf]

Assuming Only 1 of the 3 options can exist

in H2 put
=IF( OR ( I2 = "Yes – Midwest", I2 = "Yes – NE/SW/CAN"), G2, 0)

OR
=IF( I2 = "NO", 0, G2 )

 

[JackDanIce]

I think you just need

=--(I2="No")*G2

-- converts TRUE into 1 and FALSE into 0, i.e. 1 * G2 (if not option 3) or 0 * G2 (if it is option 3)

 

[Florida1510]

Thank you Thank you Thank you JackDanlce that worked perfectly. I really appreciate everyone’s response and so quickly that’s awesome. Thanks again much appreciated. Have a great day and stay safe everyone.

 

[Eric W]

I think you just need

=--(I2="No")*G2

-- converts TRUE into 1 and FALSE into 0, i.e. 1 * G2 (if not option 3) or 0 * G2 (if it is option 3)

=(I2="No")*G2

is enough without the "--". The "*G2" part is an acceptable function to convert the TRUE/FALSE to 1/0.

 

[JackDanIce]

Thanks @Eric W I did wonder about conversions and I think in the past it's not worked when I've not used -- but will check next time I need to

 

[JackDanIce]

@Florida1510 glad you have a solution, I actually "borrowed" @etaf 's reply so kudos should be theirs!

 

[Eric W]

Thanks @Eric W I did wonder about conversions and I think in the past it's not worked when I've not used -- but will check next time I need to

In theory, any arithmetic function (+, -, *, /, or the "double unary", --) is enough to convert TRUE/FALSE to 1/0. But I've also ran into cases where that doesn't seem to work without the --, requiring that I add the --, or rearrange the formula, or even add another set of parentheses somewhere. I can usually recognize the easy cases now, but with a complicated formula, I may have to experiment.

 

[JackDanIce]

Now it makes sense why I've stuck to using them than not. To be honest, I did think it's weird "1"+0 = 1 but I was needing to use --(1=1)*1 instead of just (1=1)*1

Good to know for simple cases can be dropped, probably a trial and error approach until figure out when need to use vs not, thanks again!