IFERROR and number / currency as value_if_error

09/16/20
Answered - Pending Review

I have the following columns in my worksheet:

In the "Bookings" column, I have the following formula:

=IFERROR(INDEX({Bookings Table Total Bookings}, MATCH([email protected], {Bookings Table PF}, 0)), "")

In my "Adjusted Bookings" column, I have the following formula:

=[sbe_allocation]@row * [email protected]

However, as you can see, I get the #INVALID OPERATION message when the value_if_error returns a blank. I tried modifying the formula as follows:

=IFERROR(INDEX({Bookings Table Total Bookings}, MATCH([email protected], {Bookings Table PF}, 0)), "$0")

but Smartsheet treats the "$0" as text rather than a currency value. So, then I tried to enter the value_if_error as "0," but Smartsheet also treats this as text, and the #INVALID OPERATION error does not resolve.

Is there a workaround here? Like an "ISNUMBER" wrapper for the value_if_error?

Popular Tags:

Answers

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

    Hi @Danielle Arteaga

    Try removing the ”” around the 0 so it looks something like below.

    =IFERROR(INDEX({Bookings Table Total Bookings}, MATCH([email protected], {Bookings Table PF}, 0)), 0)

    Did that work?

    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!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • Thanks, Andrée. I reverted back to leaving the cell blank for my value_if_error and putting the Adjusted bookings total into this formula:

    =IF(ISBLANK([email protected]), "", ([sbe_allocation]@row * [email protected]))

    That seemed to work, and it's better for me to have the blanks than 0s in those cells so I can distinguish products that genuinely have $0 in bookings versus products for which we are missing bookings data.

    Thanks as always for the help!

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

    @Danielle Arteaga

    Excellent!

    You're more than welcome!

    Remember! 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!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

Sign In or Register to comment.