• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need help with grotesque formula :)

MusterDuster

New Member
Hi All,

Hope you're healthy and well. Long time fan of Chandoo...learned a ton from him, now it's my turn to ask a question :)


I need help with this nasty, nasty formula...no dummy data for now, as doing both that and correcting the syntax of the formula would be very time consuming.

Here are the issues:

1) The issue is that the formula below is only pulling "negative" formula parameters (For example, as stated below in formula, "Invalid CID/Account Setup Incomplete"...highlighted in red) The formula is NOT picking up instances where a positive parameter is met (For example, as stated above in the formula, "Valid CID/Account Setup, Website Working"...also highlighted in red) Because of this, it makes me thing it's a logic issue with the formula and it's stopping somewhere before going through all the pieces of the formula.

2) The formula is not acting like a proper array formula...requires to be drug down the column, which it shouldn't require.

Below is the formula separated for readability

--------


{(IF(P2<>"",IF(AD2<>"Yes",IF(AND(X2="Verified",Z2="Yes",AA2="Yes"), IF(W2="Yes","VALID LICENSE: "&Y2 &" "&"VALID REGISTRATION: "&Y2&" "&"VALID CID / ACCOUNT SETUP: "&Z2&" "&"WEBSITE WORKING: "&AA2,

IF(W2="Not Required","LICENSE: NOT REQUIRED" &" "&"VALID REGISTRATION: "&Y2&" "&"VALID CID / ACCOUNT SETUP: "&Z2&" "&"WEBSITE WORKING: "&AA2,

IF(W2="Missing","LICENSE ISSUE, ", IF(W2="No","LICENSE ISSUE, ","")))),IF(X2="Not Verified","SoS REGISTRATION ISSUE, ", IF(Z2="No","INVALID CID / ACCOUNT SETUP INCOMPLETE, ",IF(AA2:AA="No","WEBSITE NOT WORKING","")))),""),""))}
 
Sample data is what we need...
Monster formula like this are rarely a good idea. Consider using helper columns.
 
Post a sample sheet with manually calculated expected results?
Your syntax is strange and an array formula with IF functions?
 
That is not an array formula and therefore will not spill. It's also invalid syntax due to the IF(AA2:AA="No" part, which is Google Sheets syntax.
 
Back
Top