Formula field error

Question

I have a formula field created that should be displaying Red, Yellow, or Green (words not colors), and I'm getting a strange error that I just can't suss out. The problem comes after the word "Green" in the If statement that follows to determine Yellow or Red.

The Rank__c field is a picklist with individual values from 1 to 50, then ranged values such as "51 - 75". If the Account is ranked 1-50, or has one of the excluded Text values, it all works fine. If I use one of the ranged picklist values, the Green will calculate correct, but if it should be Yellow or Red, I get "#Error!" instead. Any help identifying where I have gone wrong is greatly appreciated.

IF(
NOT(
  OR(
    ISPICKVAL(Account__r.Rank__c, "Text1"),
    ISPICKVAL(Account__r.Rank__c, "Text2"),
    ISPICKVAL(Account__r.Rank__c, "Text3"),
    ISPICKVAL(Account__r.Rank__c, "Text4"),
    ISPICKVAL(Account__r.Rank__c, "Text5"),
    ISBLANK(TEXT(Account__r.Rank__c))
  )
),
IF(
  OR(
    AND(
     ISPICKVAL(Account__r.Rank__c, "201 - 300"),
      OR(
        SalesNumber1__c >=5000,
        SalesNumber2__c >=15000
      )
    ),
    AND(
      OR(
       ISPICKVAL(Account__r.Rank__c, "101 - 150"),
       ISPICKVAL(Account__r.Rank__c, "151 - 200")
      ),
      OR(
        SalesNumber1__c >=10000,
        SalesNumber2__c >=30000
      )
    ),
    AND(
     OR(
       ISPICKVAL(Account__r.Rank__c, "51 - 75"),
       ISPICKVAL(Account__r.Rank__c, "76 - 100")
      ),
      OR(
        SalesNumber1__c >=20000,
        SalesNumber2__c >=60000
      )
    ),
    AND(
      VALUE(TEXT(Account__r.Rank__c)) <=50,
      OR(
        SalesNumber1__c >=30000,
        SalesNumber2__c >=90000
      )
    )
  ), 
  "Green",
  IF(
    OR(
      SalesNumber1__c >0,
      SalesNumber2__c >0
    ),
    "Yellow",
    "Red"
  )
),
"N/A"

)

Edited to include fix

AND(
      **VALUE(TEXT(Account__r.Rank__c)) <=50, **was trying to convert a non numerical value to a number, changed line to
      NOT(CONTAINS(TEXT(Account__r.Rank__c), "-")), 
      OR(
        SalesNumber1__c >=30000,
        SalesNumber2__c >=90000
      )
    )

Answer 1

You are getting error in this formula VALUE(TEXT(Account__r.Rank__c)) when Account__r.Rank__c is a range.

Replace this block

AND(
    VALUE(TEXT(Account__r.Rank__c)) <=50,
    OR(
        SalesNumber1__c >=30000,
        SalesNumber2__c >=90000
    )
)

with the following:

AND(
    NOT(CONTAINS(TEXT(Account__r.Rank__c), "-")),
    OR(
        SalesNumber1__c >=30000,
        SalesNumber2__c >=90000
    )
)

Or you can have some more advanced logic.