# Complicated IF function

#### maidenhel

##### New Member
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.

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

#### shyy

##### Well-known Member
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

##### New Member
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

##### Well-known Member
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

##### New Member
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

##### Well-known Member
Its confusing... post a sample of your data and how want the end result to look like.

#### maidenhel

##### New Member
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

##### Well-known Member
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

##### New Member
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

##### Well-known Member
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"

Replies
8
Views
169
Replies
1
Views
490
Replies
5
Views
163
Replies
5
Views
199
Replies
7
Views
595

1,195,667
Messages
6,011,049
Members
441,580
Latest member
BornholmerBjarne

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back