The CONVERT function isn't working with arrays. But the easy solution: Add a unary PLUS sign before the array reference.
Transcript of the video:
[ ♫ jingle ♫♫ ].
Learn Excel from MrExcel podcast episode 2347.
An amazing surprise use for the unary plus sign.
Hey, Welcome back to the MrExcel netcast. I am Bill Jelen.
I had a problem earlier this week where the CONVERT function would not work with an array.
Oh my gosh, the solution is so simple! A "unary plus".
This is a word I didn't even know was a word. I ran into this problem earlier this week.
I have an array here in B2.
If i want to convert all of those Celsius to Fahrenheit. I put in the array operator (#).
It does not work. So in the earlier episode, I copied that whole formula down.
It turns out what we have to do is coerce that array into a range.
You do that with a single plus sign known as the "unary plus".
And it works! Thanks to Joe McDaid on the Excel team for this awesome tip.
Potentially, this "unary plus" is going to help dynamic arrays work with any of these functions that came over from the Analysis ToolPak in Excel 2007.
These were originally written in VBA. They are expecting a range.
They are not expecting an array.
For example EOMONTH to get the end of month.
It is not working with I3# but instead if i would say +I3#. Bingo! They work.
What an awesome easy way to expand dynamic arrays to be able to work inside of these functions here.
My request I am NOT asking for a Like. I am NOT asking for ring the bell.
I just want you to subscribe and here is why. Down below the video, there is a Merch shelf.
I am not a fan of this company.
They are slow. There is a better company.
But to use them, I need a hundred thousand subscribers.
The fact is there is an 83 percent chance right now, if you are watching this, you have NOT subscribed.
Down below the video, click Subscribe. I won't spam you. I won't bother you.
But I will get you better Merch. I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.
[ ♫♫ ] Tips and tricks get your spreadsheet fix at MrExcel.com [ ♫ ]
Learn Excel from MrExcel podcast episode 2347.
An amazing surprise use for the unary plus sign.
Hey, Welcome back to the MrExcel netcast. I am Bill Jelen.
I had a problem earlier this week where the CONVERT function would not work with an array.
Oh my gosh, the solution is so simple! A "unary plus".
This is a word I didn't even know was a word. I ran into this problem earlier this week.
I have an array here in B2.
If i want to convert all of those Celsius to Fahrenheit. I put in the array operator (#).
It does not work. So in the earlier episode, I copied that whole formula down.
It turns out what we have to do is coerce that array into a range.
You do that with a single plus sign known as the "unary plus".
And it works! Thanks to Joe McDaid on the Excel team for this awesome tip.
Potentially, this "unary plus" is going to help dynamic arrays work with any of these functions that came over from the Analysis ToolPak in Excel 2007.
These were originally written in VBA. They are expecting a range.
They are not expecting an array.
For example EOMONTH to get the end of month.
It is not working with I3# but instead if i would say +I3#. Bingo! They work.
What an awesome easy way to expand dynamic arrays to be able to work inside of these functions here.
My request I am NOT asking for a Like. I am NOT asking for ring the bell.
I just want you to subscribe and here is why. Down below the video, there is a Merch shelf.
I am not a fan of this company.
They are slow. There is a better company.
But to use them, I need a hundred thousand subscribers.
The fact is there is an 83 percent chance right now, if you are watching this, you have NOT subscribed.
Down below the video, click Subscribe. I won't spam you. I won't bother you.
But I will get you better Merch. I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.
[ ♫♫ ] Tips and tricks get your spreadsheet fix at MrExcel.com [ ♫ ]
