Your co-worker has an Excel formula and when you press Enter, you get "You've entered too many arguments for this function." You can't even get out of the formula.
Today, I show how the lowly Function Arguments dialog can help you to isolate the problem.
Today, I show how the lowly Function Arguments dialog can help you to isolate the problem.
Transcript of the video:
Excel Won't let me enter this formula. But there's an amazing tool that's here right in front of us that'll help.
Hey, welcome back to MrExcel netcast.
I'm Bill Jelen and this is episode 2464.
Alright, so someone on YouTube posts this question. I don't get to see any of this spreadsheet.
All I know is this formula is not working. So I come out here.
Looks like they're talking about CB3, so I built it just a set of numbers starting in CB3 and I try and enter this formula in the adjacent cell.
And when I press Enter, I get “You've entered too many arguments for this function”, right? Like I don't know what they're trying to do.
I'm just trying to help write and I can't. I'm like stuck at this point.
I can't even take a step back and start to draw it out with markers or highlighters or anything.
Now, if you're here and you don't want to lose your work, just put a little apostrophe before it and you can get at least get out of edit mode and start to try and test some things.
But the tool that I found to point to where the problem is is right in front of us.
You know, if you're editing a formula, I'm going to click up here in the formula bar inside this first IF and then click on Insert Function. It takes you directly into Function Arguments.
And what's really cool here is if you have Function Arguments displayed, you can click in any part of the formula for that item to show up, right?
So right now I'm in that first if statement and it says that the third argument is invalid.
So then I go basically to the third argument. And look at this.
So if it's supposed to have a logical test, value if true, value if false.
But there's a fourth argument here, which is unknown.
It shouldn't be there and is invalid. So that's what's causing our problem.
This allows me to now see, “well, alright that CP3 shouldn't be there”.
You know that there's a lot of issues in this formula, and we'll deal with those issues in another video.
The beautiful thing I just wanted to point out is that this Function Arguments is pretty amazing. And that you can click anywhere in the formula.
There's another one that has invalid and try and solve it.
Now, later I'm going to do a video about how to do these nested IF statements and we'll unpack this formula, but that's not the point of today.
The point of today is your coworker says, Help, I got this crazy formula, I can't enter it”.
Remember, you know, just click inside the first function, click on Insert Function.
The Function Arguments appears and you know at least you can kind of click through and see which part of this is the part that is causing a problem.
I know a lot of times I'm here complaining about something in Excel, but this is one.
Whoever programmed this Function Arguments.
And is able to deal with functions that don't exist or too many arguments.
It's a really helpful tool, so kudos, kudos to the Excel team for the beautiful little Function Arguments here.
I know a lot of times people say, “oh, I'm advanced, I don't need Function Arguments”, but in this case it really helped me figure out what was happening.
Now, If you love Excel, check out my new courses on the Retrieve platform.
They are video courses, but you just type what you're looking for.
It takes you right to that spot in the video and there's a complete transcript in several languages. It's a super fast way to learn.
If you like these videos, please, down below, Like, Subscribe, and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
Alright, well hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel. Let's hear you Nancy.
Hey, welcome back to MrExcel netcast.
I'm Bill Jelen and this is episode 2464.
Alright, so someone on YouTube posts this question. I don't get to see any of this spreadsheet.
All I know is this formula is not working. So I come out here.
Looks like they're talking about CB3, so I built it just a set of numbers starting in CB3 and I try and enter this formula in the adjacent cell.
And when I press Enter, I get “You've entered too many arguments for this function”, right? Like I don't know what they're trying to do.
I'm just trying to help write and I can't. I'm like stuck at this point.
I can't even take a step back and start to draw it out with markers or highlighters or anything.
Now, if you're here and you don't want to lose your work, just put a little apostrophe before it and you can get at least get out of edit mode and start to try and test some things.
But the tool that I found to point to where the problem is is right in front of us.
You know, if you're editing a formula, I'm going to click up here in the formula bar inside this first IF and then click on Insert Function. It takes you directly into Function Arguments.
And what's really cool here is if you have Function Arguments displayed, you can click in any part of the formula for that item to show up, right?
So right now I'm in that first if statement and it says that the third argument is invalid.
So then I go basically to the third argument. And look at this.
So if it's supposed to have a logical test, value if true, value if false.
But there's a fourth argument here, which is unknown.
It shouldn't be there and is invalid. So that's what's causing our problem.
This allows me to now see, “well, alright that CP3 shouldn't be there”.
You know that there's a lot of issues in this formula, and we'll deal with those issues in another video.
The beautiful thing I just wanted to point out is that this Function Arguments is pretty amazing. And that you can click anywhere in the formula.
There's another one that has invalid and try and solve it.
Now, later I'm going to do a video about how to do these nested IF statements and we'll unpack this formula, but that's not the point of today.
The point of today is your coworker says, Help, I got this crazy formula, I can't enter it”.
Remember, you know, just click inside the first function, click on Insert Function.
The Function Arguments appears and you know at least you can kind of click through and see which part of this is the part that is causing a problem.
I know a lot of times I'm here complaining about something in Excel, but this is one.
Whoever programmed this Function Arguments.
And is able to deal with functions that don't exist or too many arguments.
It's a really helpful tool, so kudos, kudos to the Excel team for the beautiful little Function Arguments here.
I know a lot of times people say, “oh, I'm advanced, I don't need Function Arguments”, but in this case it really helped me figure out what was happening.
Now, If you love Excel, check out my new courses on the Retrieve platform.
They are video courses, but you just type what you're looking for.
It takes you right to that spot in the video and there's a complete transcript in several languages. It's a super fast way to learn.
If you like these videos, please, down below, Like, Subscribe, and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
Alright, well hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel. Let's hear you Nancy.
