Complicated IF function

[maidenhel]

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!

 

[shyy]

Try

=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"))))

 

[maidenhel]

thank you, shyy! worked like a charm. do you have any ideas for the 2nd part? 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.

 

[shyy]

Welcome to the Board!!

What is G-RES and E-Res name ranges?

This would all have to be entered in the same cell, unless you are doing VBA you can't change the contents of another cell from the active cell.

Post up a sample and the desired end result.

 

[maidenhel]

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?

 

[shyy]

Its confusing... post a sample of your data and how want the end result to look like.

 

[maidenhel]

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:

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.

 

[shyy]

here is just some syntax info:

to do an AND with a IF statement

=IF(AND(E718="E-RES",G718>0),"TRUE","FALSE")

Explain in english each criteria that you need. You can work step by step.

ex: If E718 = ERES and G718 > 0 to display something

 

[maidenhel]

i got the answer with a little help at another Excel forum.

this worked:

=IF(E718="E-RES",_IF(G718>0,"NO PITCH","ELECTRIC")_,IF(E718="G-RES",_IF(F718>0,"NO PITCH","GAS")_,IF(F718>0,IF(G718>0,"NO PITCH","ELECTRIC"),IF(G718>0,"GAS","DUAL"))))

 

[shyy]

glad you got it fixed, also you should always post the link to the other board or that could make you look bad if someone posts that link instead of you. May not be intentional but now you know...

What does the "_" do? I never seen a formula using a "_IF"