IFERROR Formula

Glen Urquhart
Glen Urquhart ✭✭✭✭✭✭
edited 03/16/21 in Formulas and Functions

Hi All,

Hope you're keeping well.


I've recently started playing with IFERROR's and updating some existing sheets.

I have a sheet that reports with each row being a week of the year. Many of the formulas for "Rows in the future" or in some case, "Rows in the Past" return DIV0 or similar errors which I'm trying to eliminate.

Im struggling with the below formula - any recomendations / advice as to where an IFERROR should be inserted?

This formula is in the red squared cell.


For interest, the formula is used to calulate an adjusted 'rolling' sales target for a salesperson, by deducting Sales in so far for the month from their original month sales target, and dividing by the number of weeks remaining in the month.


=([Total Sales Target]35 - [Total Sales]35) / COUNTIF([Week number start date]36:[Week number start date]40, >TODAY(-7))



Any advice appreciated!


Cheers, Glen.

Best Answer

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓

    Hi @Glen Urquhart

    I hope you're well and safe!

    I think Bassam missed removing the @ symbols in two places.

    Try this.

    =IFERROR([Total Sales Target]35 - [Total Sales]35 / (COUNTIF([Week number start date]36:[Week number start date]40, <TODAY(-7))), "")

    I hope that helps!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 03/17/21

    Hi @Glen Urquhart

    Hope you are fine, please try the following formula:

    =IFERROR([Total Sales Target]@35 - [Total Sales]@35 / (COUNTIF([Week number start date]36:[Week number start date]40, <TODAY(-7))), "")

    PMP Certified

    [email protected]

    www.mobilproject.it

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Glen Urquhart
    Glen Urquhart ✭✭✭✭✭✭

    Hi Bassam,

    Thanks for your message.

    Thats the format that I've used for all other columns, sandwiching the original formula with the IFERROR, but unfortunatly its #UNPARSEABLE for this one.

    (I just double checked using your version too)


    Thank you for your help anyway!


    Cheers,

    Glen.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓

    Hi @Glen Urquhart

    I hope you're well and safe!

    I think Bassam missed removing the @ symbols in two places.

    Try this.

    =IFERROR([Total Sales Target]35 - [Total Sales]35 / (COUNTIF([Week number start date]36:[Week number start date]40, <TODAY(-7))), "")

    I hope that helps!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Glen Urquhart
    Glen Urquhart ✭✭✭✭✭✭

    Thank you for commenting Andrée.



    This version was working, but not giving the answer I'd hoped for...

    In typing my response I also noticed another error I had put in last night meaning the math wasn't following the route I'd expected - I'd missed out a couple of brackets.


    =IFERROR(([Total Sales Target]35 - [Total Sales]35) / (COUNTIF([Week number end date]36:[Week number end date]40, >=TODAY())), "Month Concluded")


    The final formula is as above, and this works as it should.


    Thank you both for your help, hope you have a great day :)


    Cheers, Glen.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @Glen Urquhart

    Excellent!

    You're more than welcome!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.