Need help to unravel nested if statement in formula. (fomula to VBA code)

Richard U

Active Member
Joined
Feb 14, 2006
Messages
402
Office Version
  1. 365
  2. 2016
  3. 2010
  4. 2007
Platform
  1. Windows
I have this formula

=IF(OR((IF(‘Sheet 1’!AJ6=0;""; ‘Sheet 1'!AJ6))="";(IF(‘Sheet 1'!AJ6=0;""; ‘Sheet 1'!AJ6))=" ");"~";IF(LEFT(‘Sheet 1'!AM6;3)="940";"RSW INPUT";IF(LEFT(‘Sheet 1'!AM6;3)="401";"REU INPUT";IF(LEFT(‘Sheet 1'!AM6;3)="200";"ANZ INPUT";IF(RIGHT($M6;2)="MX";"#N/A";IF(‘Sheet 1'!AN6="COMPANY NAME";"RNA INPUT";VLOOKUP(E6&" "&(IF‘Sheet 1'!AJ6=0;"";'Portal Data'!AJ6));Sarep!$B$4:$C$291;2;"FALSE")))))))


Which I need to translate into some maintainable VBA code, but I can't quite figure out what this is doing. I'm a good coder, but the nested excel formulas confuse me.


Could someone please explain what this formula is doing? I can write the code myself once I understand the logic, but I can't seem to unravel this myself
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

cerfani

Well-known Member
Joined
Dec 15, 2014
Messages
1,136
You have a few syntax errors... you are using semi colons instead of commas?? You are missing a parentheses on an If function.

If you want to clean it up, you should remove the extra parentheses you have around IF functions. You are doing this...

(IF(...))

Stuff like that is making it harder to read.
 

Richard U

Active Member
Joined
Feb 14, 2006
Messages
402
Office Version
  1. 365
  2. 2016
  3. 2010
  4. 2007
Platform
  1. Windows
It's not my formula
 

craigexcel

Active Member
Joined
Jun 28, 2006
Messages
285
Office Version
  1. 2016
Platform
  1. Windows
To the contrary, in the context of this forum, it's defintely your formula. As cerfani suggests, I would try to clean up the formula as much as possible, so it's easier to read, and then repost. You're more likely to get feedback as a result.
 

cerfani

Well-known Member
Joined
Dec 15, 2014
Messages
1,136

ADVERTISEMENT

maybe this will help you... i fixed the syntax error... i separated all the nested functions to look more like vba code... you have the boolean test on first line then the next line is the true result and after that the false result which leads to more nested IFs

c0UvvAE.png
 

cerfani

Well-known Member
Joined
Dec 15, 2014
Messages
1,136
The IFs in that OR function were kinda messy...

OZSuoXw.png


just fyi: the value "" is different that the value " " ... a space is a unique character... this is an empty string value "" ... this is not empty " " ;)
 
Last edited:

thisoldman

Well-known Member
Joined
Jan 5, 2014
Messages
1,074

ADVERTISEMENT

There misplaced parentheses in the IF following the VLOOKUP and unneeded quotation marks. I think this is how that subformula should be entered:
VLOOKUP(E6 & " " & IF(‘Sheet 1'!AJ6=0; ""; 'Portal Data'!AJ6); Sarep!$B$4:$C$291; 2; FALSE)

The OR() at the beginning doesn't make sense to me:
OR((IF(‘Sheet 1’!AJ6=0;""; ‘Sheet 1'!AJ6))=""; (IF(‘Sheet 1'!AJ6=0; ""; ‘Sheet 1'!AJ6))=" ")

I think this is meant to test if 'Sheet 1'AJ6 is a zero, a blank, an empty string, or a space. If I am correct, we can replace it with
OR('Sheet 1'AJ6=0; 'Sheet 1'AJ6=""; 'Sheet 1'AJ6=" ")

Formatted to give some structure:
Code:
=IF(OR('Sheet 1'AJ6=0; 'Sheet 1'AJ6=""; 'Sheet 1'AJ6=" ");
    "~";
    IF(LEFT(‘Sheet 1'!AM6;3)="940";
      "RSW INPUT";
      IF(LEFT(‘Sheet 1'!AM6;3)="401";
        "REU INPUT";
        IF(LEFT(‘Sheet 1'!AM6;3)="200";
          "ANZ INPUT";
          IF(RIGHT($M6;2)="MX";
            "#N/A";
            IF(‘Sheet 1'!AN6="COMPANY NAME";
              "RNA INPUT";
              VLOOKUP(E6 & " " & IF(‘Sheet 1'!AJ6=0; ""; 'Portal Data'!AJ6); Sarep!$B$4:$C$291; 2; FALSE)))))))

That should help.
 

Richard U

Active Member
Joined
Feb 14, 2006
Messages
402
Office Version
  1. 365
  2. 2016
  3. 2010
  4. 2007
Platform
  1. Windows
second try thanks for the input.

=IF(OR(IF('Portal Data'!AJ6=0,"",'Portal Data'!AJ6)="",IF('Portal Data'!AJ6=0,"",'Portal Data'!AJ6)=" "),VLOOKUP(AH6,Sarep!$G$4:$H$500,2,"FALSE"),IF(LEFT('Portal Data'!AM6,3)="940","ACO",IF(RIGHT($M6,2)="MX","#N/A",IF(OR(LEFT('Portal Data'!AM6,3)="401",LEFT('Portal Data'!AM6,3)="200",'Portal Data'!AN6="Company Name"),"SFC100",IF(J6="INPUT","RNA",VLOOKUP(E6&" "& IF('Portal Data'!AJ6=0,"",'Portal Data'!AJ6),Sarep!$B$4:$D$500,3,"FALSE"))))))
 

Richard U

Active Member
Joined
Feb 14, 2006
Messages
402
Office Version
  1. 365
  2. 2016
  3. 2010
  4. 2007
Platform
  1. Windows
Wow, thanks to all of you! THANK YOU SO MUCH!
 

cerfani

Well-known Member
Joined
Dec 15, 2014
Messages
1,136
Regarding the OR function, it can be optimized...

Code:
OR(IF(‘Sheet 1’!AJ6=0,"", ‘Sheet 1'!AJ6)="",IF(‘Sheet 1'!AJ6=0,"", ‘Sheet 1'!AJ6)=" ")

can instead be...

Code:
‘Sheet 1’!AJ6=0

you notice it will result in true, if the IF results in "" which would occur if ‘Sheet 1’!AJ6=0 was true... so you can skip the extra steps and just check... ‘Sheet 1’!AJ6=0
 

Forum statistics

Threads
1,147,731
Messages
5,742,852
Members
423,758
Latest member
accountfornada

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top