IFERROR Formula

Glen Urquhart

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.

Andrée Starå

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!

Bassam Khalil

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))), "")

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å

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!

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å

@Glen Urquhart

Excellent!

You're more than welcome!