Error when using roundup and index in same formula

[Sylver75]

I've searched your forum, and this post came close, but not quite the same: Spill dynamic array across multiple columns with a defined depth.
I have an excel sheet that looks as below:

my current formula:
=INDEX(Business!B2:L7, MATCH(B3,Business!B2:B7,0)9)*100
Check a designated list against column B, and gives the correct % results (on example above, it would check Description B and return results 35).

I tried to add a roundup to it: =ROUNDUP(INDEX(Business!B2:L7, MATCH(B3,Business!B2:B7,0)),9)*100 but I get error #REF.
When I evaluate the steps, it shows:
=ROUNDUP(INDEX(Business!B2:L7, MATCH(B3,Business!B2:B7,0)),9)*100

=ROUNDUP(INDEX(Business!B2:L7, MATCH(B,Business!B2:B7,0)),9)*100

=ROUNDUP(INDEX(Business!B2:L7,2),9)*100

=ROUNDUP(#REF!,9)*100

#REF!*100

#REF!

I think the problem might be in the second parenthesis added after number 0, however if I try adding the parenthesis at the end or anywhere else, I get an error: "You've entered too few arguments for this function".

Any help is appreciated.

 

[Joe4]

The reason why it did not work had nothing to do with the ROUNDUP part.
Your original formula was NOT a working formula (it had an unbalanced number of parentheses).

Note that you could have simply removed the parentheses after the "*100" too, i.e.

=ROUNDUP(INDEX(Business!B2:L7, MATCH(B3,Business!B2:B7,0),9)*100,0)

A good rule of thumb for debugging these sort of nested formulas is to work from the "inside-out".
Start off the MATCH formula.
When you get that working, then add the INDEX part.
And then when you get that working, add the ROUNDUP part.

 

[Sylver75]

The reason why it did not work had nothing to do with the ROUNDUP part.
Your original formula was NOT a working formula (it had an unbalanced number of parentheses).

Note that you could have simply removed the parentheses after the "*100" too, i.e.

=ROUNDUP(INDEX(Business!B2:L7, MATCH(B3,Business!B2:B7,0),9)*100,0)

A good rule of thumb for debugging these sort of nested formulas is to work from the "inside-out".
Start off the MATCH formula.
When you get that working, then add the INDEX part.
And then when you get that working, add the ROUNDUP part.

You are correct. My original ROUNDUP formula had a parenthesis in the wrong place. Because any other place I added the closing parenthesis, would just give me a popup that said: "You've entered too few arguments for this function" (as I was missing the second argument and was unawares if it), but adding it right after the 0 caused the REF error. As the idiot that I am, I though that meant it was the right place, and there was something else wrong with it. Learning lesson.

 

[Joe4]

You are correct. My original ROUNDUP formula had a parenthesis in the wrong place. Because any other place I added the closing parenthesis, would just give me a popup that said: "You've entered too few arguments for this function" (as I was missing the second argument and was unawares if it), but adding it right after the 0 caused the REF error. As the idiot that I am, I though that meant it was the right place, and there was something else wrong with it. Learning lesson.

Yeah, I find sometimes when there is a lot of stuff going on, it is easy to not "see the forest for the trees".
It those cases, I usually find it works best to start from the beginning, and build your way up.
Then it is much easier to see exactly what might be causing the error.