France Maya May 2, 2021 Spreadsheet
When Microsoft Excel is used to manipulate, store and analyse data it can become extremely difficult to manage, let alone efficiently work to produce any meaningful insights. This is because with data sets large and small, the data must be meaningful, logical, structured, internally consistent and clean. This holds true regardless of whether the data has been imported into excel from another system or manually entered. In this computing age, most people know that for any data set to be useable it must first be relatively structured and clean. A spreadsheet and its table layout naturally encourages data to be somewhat structured, however ensuring data is clean is also difficult.
There seems to be a move on the Internet to have only terminals for Internet users and all the hard drive would be saved at giant Internet hubs. Microsoft would like to have all their programs at get their location and users would pay a monthly subscription fee for things like Microsoft Word and Microsoft XL. This way people could do there creating at their terminal and all the data would be backed up that Microsoft. Also, everyone could interface together since they all had the latest version with the latest features. It makes a lot of sense to do it this way.
In a well-designed spreadsheet, any output can be calculated from the raw data. However, that‘s not always enough. Sometimes the output is fixed and the raw data is variable. Let‘s say you run an investment company and want to offer your clients a fixed return. An Excel expert could create a very complex model to calculate the likely return on investments over a fixed period. You could then calculate the internal rate of return being offered to clients. The problem is that you‘re not interested in the return offered to clients; that is, after all, fixed. Instead you‘re concerned with how much money you expect to draw from the investment fund, whilst still offering your investors a satisfactory return. If you have $1 and owe investors a quarter, you can calculate your profits using a simple formula.
Microsoft Excel is a phenomenally powerful calculator. You can create spreadsheets with 10,000 lines of data and calculate subtotals instantly. Indeed, if you change your data, any totals will get automatically updated. Arguably that‘s not too impressive. If we have quarterly revenues of $1m, and we secure another $20k, we can update our subtotal without summing revenues from scratch. So it‘s more impressive that Excel can do the same thing with statistical functions. If you‘ve ever plotted a chart on Excel, you may be aware that you can add a best fit line. These best fit lines are calculated using a method known as regression. Basically, you have to calculate the distance of every single point from the line, and minimise the sum. The maths is a little more sophisticated but the key point is that, every time you change the data, you need to perform the analysis all over again.
Whilst Excel cannot clean or structure all of your data for you it does come with some useful functionality for manipulating and analysing clean and structured data sets. This in-built functionality includes pivot tables, sorting and filtering. Filtering alone is a powerful tool and can help to quickly isolate data based on specified criteria. But what happens if your data is clean but not very structured (a common problem). For instance what if you, a client or your team is using colours, fonts or some kind of formatting to classify data in an Excel spreadsheet. In short, you wont be able to filter the data, because Excel‘s in-built filtering logic requires rules based on numbers, dates and text only. It will not perform filtering based on formats. In addition Excel filtering only applies down rows. It will not perform filtering across columns.
Lester P. Goodbinder had suffered another agonizing week in Pittsburgh. The semi-annual audit he conducted at the Bourgeois Ball Bearing Factory stretched into five 14-hour days examining electronic spreadsheets on an archaic computer system installed in the early ‘80s. The equipment churned so abysmally he cleverly joked to himself it was powered by lazy hamsters on treadmills. Not only that, the accounting software loaded on the system was an early version of ”Abacus,” and only slightly faster than a key-punch adding machine but considerably slower than a hand-held calculator.
Tag Cloudfree budget spreadsheets