Complicated IF function

Hello All,

I am new to this site in hopes of a little help.

Here's the quick scenario:

I have 2 columns that I would like to compare. They start at F4 and G4 respectively. Here's what I am trying to achieve:

IF
F4>0, G4>0 display NO PITCH
F4=0, G4>0 display GAS
F4>0, G4=0 display ELECTRIC
F4=0, G4=0 display DUAL

I have tried tirelessly to get the result column to display correctly, to no avail.

As another challenge, I would like this result to be compared to an additional column, call it E4. There are 3 results in E4, COMBINE, E-RES, G-RES. COMBINE has no effect on the result. E-RES would negate GAS, and turn DUAL into ELECTRIC. G-RES would negate ELECTRIC, and turn DUAL into GAS.

If this second part could be integrated as well, it would so greatly appreciated, but I understand it complicates things quite a bit.

Thank you in advance!

you need to do something like if(and(f4>0,G4>0),"No Pitch",if(and(f4=0,g4>0,"Gas"),if(and(F4>0,G4=0),"Electric",if(and(f4=0,G4=0)),"Dual","Something else")))))
Also, you can add E4 to the and function

Have fun

You could also use hidden columns. One column for each comparison. Say, H4 = If(And(F4=0,G4>0),"Gas","")... J4 = If(And(F4>0,G4>0),"Electric","") etc. Do a new column for each comparison. Then, the last column you could concatenate all the test columns to get the final answer. K4=H4 & J4. 'H4 & J4' is the same as =CONCATENATE(H4,J4). Since all but one will be Null, it will only show what you want. Hide all columns for each comparison.

thank you guys so much for the quick replies!

so far i've used this: =IF(AND(F4>0,G4>0),"NO PITCH",IF(AND(F4=0,G4>0),"GAS",IF(AND(F4>0,G4=0),"ELECTRIC",IF(AND(F4=0,G4=0),"DUAL"))))

it worked like a charm.

for the 2nd part, i shouldn't have used the word "negate". basically, if the value in E4 equals E-RES, then i want the result column from above to read "ELECTRIC" regardless of the F4 and G4 formulas. so it would override the other result. G-RES would display "GAS". most of the values in column E are 'COMBINE', which won't effect the result column.

does this clarify enough?

what if i simplified the 2nd part to simply be:

IF
E4=E-RES,G4>0, "NO PITCH"
E4=G-RES,F4.0, "NO PITCH"

i tried adding these to what i've used so far =IF(AND(F4>0,G4>0),"NO PITCH",IF(AND(F4=0,G4>0),"GAS",IF(AND(F4>0,G4=0),"ELECTRIC",IF(AND(F4=0,G4=0),"DUAL"))))
and i keep getting errors.

this is exactly what i've input:
=IF(AND(F4>0,G4>0),"NO PITCH",IF(AND(F4=0,G4>0),"GAS",IF(AND(F4>0,G4=0),"ELECTRIC",IF(AND(F4=0,G4=0),"DUAL",IF(AND(E4=E-RES,G4>0,"NO PITCH",IF(AND(E4=G-RES,F4>0,"NO PITCH"))))))))

can you guys troubleshoot this for me? am i doing something wrong?

thanks again for your all of your help! what a great "welcome to the forum". i added reputation and marked this thread as SOLVED.

so i'm back because i don't think i have my problem resolved just yet. i am using this function: =IF(E718="E-RES","ELECTRIC",IF(E718="G-RES","GAS",IF(F718>0,IF(G718>0,"NO PITCH","ELECTRIC"),IF(G718>0,"GAS","DUAL"))))

here's the issue i am still having. hopefully this image shows up:

Untitled.png

so basically, i want H718 to display "NO PITCH" because E718=E-RES **AND** G718>0. The same would apply if E718="G-RES" **AND** F718>0.

can anyone tell from my function where i am going wrong? or what i might need to add? i've been playing around with this forever and i've come up empty-handed, so any help would be amazing.

you're a genius! worked like a charm. thank you so much!