I have used Excel for 25 years; Office and Excel for Mac for 8 years. With the increased need for real-time/relevant data, I decided to replace the old 'copy/paste' HTML data with a more efficient process and turned to YouTube videos to learn how whereupon I am met with the first challenge. When I click "from HTML data" button in Excel, instead of a dialogue box with a field for the destination URL, I am forced to "connect a cloud service or sharepoint location to my account".
Shouldn't there be an option to connect to website or destination URL?
The second issue derives from using the "Rank" function with the "countif" function to uniquely rank a set of data (no ties). For data in cells A1 to A10, the standard formula is: rank(A1,$A$1:$A$10,0)+Countif($A$1:A1,A1)+1
As seen in screenshots, the same formula uniquely ranks two cells both with the value of $0.2 but fails to create unique ranks for two cells both with value $3.2.
On a side note, often times using the copy/paste method, I end up with numbers stored as text or other data that Microsoft Excel cannot convert to a number using any of the standard methods:
- Copy, paste special, Add 0 to an array
- Copy, paste special, Multiply by 1
- use formula "=substitute([any cell reference],char(160),char(32)) which should replace HTML space with standard space or
- use formula "=substitute([any cell reference],char(160),"") which should replace HTML space [char(160)] with nothing
Are there other ways of overcoming these character and formatting issues? Are there ways to avoid them at all in the future? So far, my experience has been that it is only slightly more inefficient to hand tab all of the data vs the lengthy process of deconstructing the myriad issues posed by the conversion process. I am happy to be wrong in this assessment or to learn any new method that makes the conversion from HTML or any other program language to Excel for Mac useful. Moreover, I am trying to identify reliable sources for documentation on what those processes are. Because Microsoft Office365 support for Office for Mac applications is in need of a user interface design and experience review process (usability, etc.) that is as exhaustive as the Office365 for PC process. As of now, it seems like a series of dead ends identified only by the user, once he/she deduces that a given function, formula, or feature is, in fact, no longer supported or never was supported within the Office365 for Mac product. I look forward tot he day when Microsoft cares enough about the Mac user base to invest thought leadership and resources into the Office365 for Mac customer experience.
Thank you - and Happy Thanksgiving!
Stacey
