Unparsable

Aaron M
Aaron M
edited 09/02/22 in Formulas and Functions

Hello all,

So, this is my formula; in excel it works and gives me the value that is correct, but in Smartsheet it says unparseable

=IF(AND([Supplier Name]@row = [Supplier Name]1, [Funding Occurrence]@row = "Annual", [Paid (Yes/No)]@row = "", [Billed (Yes/No)]@row = "", TODAY() - [Billing Date]@row = >"320"), "Bill" ,if(AND([Supplier Name]@row = [Supplier Name]1, [Funding Occurrence]@row = "Quarterly", [Paid (Yes/No)]@row = "", [Billed (Yes/No)]@row = "", TODAY() - [Billing Date]@row = >"60"),"Bill", "Don't' Bill")))

=IF(([Funding Occurrence]@row = "Annual", "365", IF([Funding Occurrence]@row = "Quarterly", "90", 0)) + [Billing Date]1))))

Both of these formulas are coming back as unparseable.

Also, when I go to drag down the formula, it looks to be anchoring certain cells when they don't need to be.

Answers

  • Sameer Karkhanis
    Sameer Karkhanis ✭✭✭✭✭✭
    edited 09/02/22

    You have brackets at incorrect places in both your formulas and incorrect data type when comparing numbers. Try below,

    =IF(AND([Supplier Name]@row = [Supplier Name]1, 
            [Funding Occurrence]@row = "Annual", 
            [Paid (Yes/No)]@row = "", 
            [Billed (Yes/No)]@row = "", 
            TODAY() - [Billing Date]@row =>320), 
            "Bill" ,
            IF(AND([Supplier Name]@row = [Supplier Name]1, 
                [Funding Occurrence]@row = "Quarterly", 
                [Paid (Yes/No)]@row = "", 
                [Billed (Yes/No)]@row = "", 
                TODAY() - [Billing Date]@row =>60), "Bill", "Don't' Bill"
            )
        )
    
    =IF([Funding Occurrence]@row = "Annual", 365, 
        IF([Funding Occurrence]@row = "Quarterly", 90, 0)) + [Billing Date]1
    

    Regarding the anchoring of cells, I don't know what your requirement is but in the above formula when you are comparing values with [Supplier Name]1 then when you drag the formula down the column this will change to [Supplier Name]2, [Supplier Name]3..... and same with [Billing Date]1. In case you want to always compare with the value in the first cell then you will need to change that to [Supplier Name]$1

  • So, I was able to get the billing date formula to work=IF([Funding Occurrence]@row = "Annual", 365 + [Billing Date]1, IF([Funding Occurrence]@row = "Quarterly", 90, 0 + [Billing Date]1))


    But I still can't get my billing formula to work:

    =IF(AND([Supplier Name]@row = [Supplier Name]1, [Funding Occurrence]@row = "Annual", [Paid (Yes/No)]@row = "", [Billed (Yes/No)]@row = "", TODAY() - [Billing Date]@row = >320), "Bill", IF(AND([Supplier Name]@row = [Supplier Name]1, [Funding Occurrence]@row = "Quarterly", [Paid (Yes/No)]@row = "", [Billed (Yes/No)]@row = "", TODAY() - [Billing Date]@row = >60), "Bill", "Don't' Bill"))))

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Aaron M

    When you're looking for something that's both equal to and greater than (or equal to and less than) in a formula, you'll want to first put the > or < symbol and then the = sign.

    For example, you have:

    [Billing Date]@row = >320

    But you'll need to use:

    [Billing Date]@row >= 320

    See: Create and Edit Formulas in Smartsheet


    You may also want to use absolute references in your formula if you want one value to stay locked on a specific row:

    =IF(AND([Supplier Name]@row = [Supplier Name]$1

    Try something like this:

    =IF(AND([Supplier Name]@row = [Supplier Name]$1, [Funding Occurrence]@row = "Annual", [Paid (Yes/No)]@row = "", [Billed (Yes/No)]@row = "", TODAY() - [Billing Date]@row >=320), "Bill", IF(AND([Supplier Name]@row = [Supplier Name]$1, [Funding Occurrence]@row = "Quarterly", [Paid (Yes/No)]@row = "", [Billed (Yes/No)]@row = "", TODAY() - [Billing Date]@row >=60), "Bill", "Don't' Bill"))))

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!