Latest news with #dataaccuracy
Geeky Gadgets
09-07-2025
- Business
- Geeky Gadgets
Avoid These Common Mistakes with MS Excel's Percent Of Function
Have you ever trusted a tool to simplify your work, only to discover it might be quietly leading you astray? That's exactly the risk you run with Excel's 'Percent Of' function. On the surface, it promises quick and easy percentage calculations, but lurking beneath its straightforward exterior are pitfalls that could sabotage your data accuracy. Whether you're crunching sales figures or analyzing performance metrics, one misstep with this function could mean misleading results—and in the world of data, that's a mistake no one can afford. Before you let this function take center stage in your analysis, it's crucial to understand its hidden limitations. In this overview, Excel Off The Grid uncover why the 'Percent Of' function isn't always the hero it seems to be and how pairing it with tools like 'Group By' can amplify its flaws. You'll learn about the subtle ways it can distort your calculations and discover smarter alternatives to sidestep its traps. From custom lambda functions to strategies for handling non-numeric data, this guide will equip you with the insights you need to ensure your percentages are precise and reliable. By the end, you'll not only know when to avoid this function but also how to replace it with techniques that truly work for your data. Because when it comes to analysis, precision isn't optional—it's essential. Excel 'Percent Of' Limitations Understanding the 'Group By' Function The 'Group By' function in Excel is a versatile tool designed to organize and summarize data efficiently. It allows you to group rows based on specific fields and apply aggregation functions, such as sums, averages, or counts, to these groups. This capability is particularly useful when working with large datasets that require structured analysis. Key features of the 'Group By' function include: Grouping data by fields such as region, category, or date to create meaningful summaries. Applying aggregation functions like sum, average, or count to grouped data for quick insights. Compatibility with dynamic array functions, such as 'hstack,' allowing multiple calculations simultaneously. For example, if you have sales data categorized by region, you can use 'Group By' to calculate the total sales and average sales for each region in a single step. This functionality makes 'Group By' an essential tool for structuring and analyzing complex datasets effectively. How the 'Percent Of' Function Operates The 'Percent Of' function is designed to calculate percentages by comparing subsets of data to the total dataset. It works by summing the values within a subset and dividing that sum by the total sum of the dataset. For instance, if you want to determine what percentage of total sales comes from a specific product category, this function provides a straightforward solution. However, the function has a significant limitation: it aggregates the subset before performing the division. This means it does not calculate percentages for individual data points within the subset. As a result, the function can produce unexpected or misleading results, particularly when working with counts or datasets containing non-numeric data. Key limitation: The 'Percent Of' function assumes aggregated totals, which can lead to inaccuracies when applied to individual data points or non-numeric datasets. Don't Use the PERCENTOF Excel Function Until You Watch This! Watch this video on YouTube. Below are more guides on Excel functions from our extensive range of articles. Challenges When Combining 'Percent Of' with 'Group By' Using the 'Percent Of' function alongside 'Group By' can introduce errors, especially when calculating percentages of counts. The root of the issue lies in how the 'Percent Of' function processes aggregated data. Instead of working with individual data points, it calculates percentages based on pre-aggregated totals, which can distort the results. Common issues include: Inaccurate results when calculating percentages of total rows for each group, as the function assumes numeric data and specific input structures. Complications with datasets containing text or non-numeric values, as the function is not designed to handle these effectively. These challenges can lead to misleading outcomes, particularly in scenarios where precision is critical, such as financial reporting or performance analysis. Strategies to Overcome Limitations To ensure accurate calculations and avoid errors, consider adopting alternative approaches that address the limitations of the 'Percent Of' function. These strategies provide more reliable results and greater flexibility in handling diverse datasets. Custom Lambda Functions: Design a lambda function tailored to your specific needs. For example, you can create a lambda function to calculate percentages of counts by generating arrays of ones to represent row counts. This ensures calculations are based on individual data points rather than aggregated totals. Design a lambda function tailored to your specific needs. For example, you can create a lambda function to calculate percentages of counts by generating arrays of ones to represent row counts. This ensures calculations are based on individual data points rather than aggregated totals. Using 'Counta' for Non-Numeric Data: The 'counta' function is effective for handling text and other non-numeric data. By combining 'counta' with dynamic array functions, you can include all rows in your calculations, making sure comprehensive results. The 'counta' function is effective for handling text and other non-numeric data. By combining 'counta' with dynamic array functions, you can include all rows in your calculations, making sure comprehensive results. Careful Data Structuring: Organize your data and formulas to minimize errors. For instance, separate numeric and non-numeric data to avoid miscalculations and ensure clarity in your analysis. These methods allow you to bypass the inherent limitations of the 'Percent Of' function, allowing more accurate and reliable data analysis. Practical Example: Making sure Accurate Percentage Calculations Consider a scenario where you have a dataset containing sales data grouped by region. You want to calculate the percentage of total sales for each region. While the 'Group By' function paired with 'Percent Of' can handle this calculation, it may falter if you need to calculate the percentage of total rows for each region. This is because the 'Percent Of' function relies on aggregated data, which can lead to inaccuracies. To address this issue, you can: Create a custom lambda function that generates an array of ones for each row in the dataset. Summing these arrays and dividing by the total number of rows will yield accurate percentages of counts. Use the 'counta' function to include all rows, even those containing text, making sure that your calculations are both comprehensive and precise. By implementing these approaches, you can achieve accurate results tailored to the specific requirements of your analysis, regardless of the dataset's complexity. Key Insights for Effective Data Analysis The 'Percent Of' and 'Group By' functions in Excel are powerful tools, but they require careful handling to avoid errors. Misusing these functions can lead to inaccuracies, particularly when working with counts or non-numeric data. By understanding their mechanics and limitations, you can make informed decisions and achieve precise results. Key takeaways include: The 'Group By' function is invaluable for organizing and summarizing data, especially when paired with dynamic array functions. The 'Percent Of' function is useful for calculating percentages but requires careful handling to avoid inaccuracies. Custom lambda functions, 'counta,' and thoughtful data structuring are effective strategies for overcoming the limitations of these functions. By mastering these techniques, you can harness the full potential of Excel's advanced functions, making sure robust and reliable solutions for your data analysis needs. A clear understanding of these tools' strengths and weaknesses will empower you to avoid common pitfalls and unlock new possibilities in your analytical work. Media Credit: Excel Off The Grid Filed Under: Guides Latest Geeky Gadgets Deals Disclosure: Some of our articles include affiliate links. If you buy something through one of these links, Geeky Gadgets may earn an affiliate commission. Learn about our Disclosure Policy.
Fast Company
08-07-2025
- Business
- Fast Company
Out of the bad data wilderness: How to solve the data challenges slowing your AI initiatives
There's a good chance your company is stuck in the wilderness. Not the North Cascades or the Olympic Peninsula, which rise up on either side of where I live in Washington, but a data wilderness where far too many companies are stranded. They usually don't realize it, though, until they move forward with AI initiatives. One moment, they are safely on the right path, making progress toward their AI goals. The next moment, they are off trail with limited options on which direction to go. DATA QUALITY: THE BOTTLENECK TO MANY AI INITIATIVES The data wilderness hits major technology initiatives like AI projects when obstacles are created by data accuracy, quality, and accessibility. Fast Company has put a spotlight on the make-or-break impact of data quality on AI initiatives in a number of recent articles. I have also discussed this in my recent Fast Company Executive Board column. Data can suffer from many quality issues. Is it accurate? Is it precise enough and rich enough for how AI will use it? Is it accessible when you need it? Is it current? Is it in the right format? Is it properly structured? Is it compliant with evolving regulatory requirements like privacy laws? These issues arise particularly with technical data, where accuracy, precision, timeliness, and other characteristics are crucial for an organization's operations. Examples include geospatial/GIS data, infrastructure operations data, manufacturing/industrial systems data, IoT implementations data, and more, which are critical across a wide range of industries. For organizations that rely on this complex technical data, ensuring that the data is truly AI-ready can be challenging. The traditional remedies tend to be slow and costly because they rely on manual review of data sets and manual fixes to make the data points more accurate, remove errors, enrich the data, improve dataflows that fix latency issues, and so forth. Given how vital data is to every aspect of an organization's operations, investing in greater data quality pays dividends. But how can your organization accomplish this in a way that is cost- and resource-effective, while also moving at the speed needed to achieve aggressive AI initiative timelines? COULD AI ITSELF BE THE SOLUTION? My team offers a surprising answer to that question: Making data truly AI-ready may require the use of AI. To illustrate this, let's look at a municipality in Illinois that is working with TRC to deploy AI to drive operational efficiencies related to infrastructure management and delivery of public services. As is true of many organizations, an audit of its data revealed data quality issues that would stand in the way of successfully building and training the AI models. And like most organizations, this client has limited resources to address these data issues with the time-consuming techniques I discussed above. But limitations drive creativity and necessity drives invention. That is how we began experimenting with using AI to automate the process of identifying and resolving data quality problems. Based on an assessment of the quality issues we were seeing and anticipating in the municipality's data, we trained a generative AI model to review massive datasets for missing data points, outlier data points that might be errors, data latency issues that would require improved dataflows, and more. We also trained the AI model to assess data quality and precision in the most complex datasets, allowing the model to assess data with some of the same expertise and nuance that a trained professional would use in reviewing the data. After reviewing the assessments, the AI model was trained to perform fixes for common quality issues and escalate others for manual resolution. These automated steps were overseen by professionals, acting as a force multiplier that allowed a small team to perform data enhancement to massive datasets that would have been impossible otherwise. Nothing can replace the data enhancement work that a trained professional with domain expertise can perform, but these pilot projects show that AI has an important role to play in augmenting teams who face urgent timelines for ensuring that data is AI-ready. I have a career's worth of working on data enhancement projects, and I was pleasantly surprised at how effective this approach was. My team has used a similar approach with other projects, and the outcomes are equally positive. The result is a process that could prove to be a valuable blueprint for other organizations trying to make it through the data wilderness and get their AI initiatives back on track. MOVING FORWARD WITH DATA It is important that organizations understand what data quality means to them and have a data governance program that meets its own set standards for quality. Begin with a consensus about how to define data quality. This foundational definition will give your organization a clear standard for diagnosing and resolving issues in order to be AI-ready. My next column will cover this topic in depth. Until then, if you get stuck in the data wilderness on the way to achieving your AI objectives, don't worry. AI may be exactly what you need… to take full advantage of AI.
New York Times
09-05-2025
- Business
- New York Times
DOGE's Zombie Contracts: They Were Killed but Have Come Back to Life
At least 44 of the government contracts canceled on the orders of Elon Musk's cost-cutting initiative have been resurrected by federal agencies, wiping out more than $220 million of his group's purported savings, according to a New York Times analysis of federal spending data. But Mr. Musk's group continues to list 43 of those contracts as 'terminations' on its website, which it calls the 'Wall of Receipts.' The group even added some of them days or weeks after they had been resurrected. The result was another in a series of data errors on the website that made the group seem more successful in reducing government costs than it had been. The White House says that this is a paperwork lag that will be remedied. The revived contracts ranged from small-dollar agreements about software licenses to large partnerships with vendors that managed government data and records. Most of the contracts were canceled in February and March, when Mr. Musk's group, the Department of Government Efficiency, was demanding that agencies make huge cuts in spending and staff. Then agencies reinstated them, sometimes just days later. In one case, the Environmental Protection Agency revived a contract after just 2 ½ hours. Mr. Musk's group still listed that one as canceled for weeks afterward, even after it had been revived and then extended — so that it will cost more now than before. These reversals illustrated not only the struggles of Mr. Musk's team to produce accurate data about its results, but also the drawbacks of its fast, secretive approach to cutting spending as part of a sweeping effort to slash $1 trillion from the $7 trillion federal budget in a few months. Contractors said that, in its rush, Mr. Musk's group had recommended killing contracts that were unlikely to stay dead. Some were required by law. Others required skills that the government needed but did not have. Their reversals raise broader questions about how many of the Musk group's deep but hasty budget cuts will be rolled back over time, eroding its long-term effect on bureaucracy and governing in Washington. In Northern Virginia, the government contractor Larry Aldrich was notified in February that his company, BrennSys, had lost its contract to do web design and produce videos for a Department of Veterans Affairs website for veterans with post-traumatic stress disorder. 'The V.A. cannot do this work on its own,' Mr. Aldrich said. 'They don't have the manpower, or the skill set.' It did not last. 'Two weeks later, we got an email saying it was going to be reinstated,' Mr. Aldrich said. 'I was like, Wow, somebody must have gone back and told them, 'We can't do this.'' A White House spokesman, Harrison Fields, said the reversals showed that agencies had re-evaluated cuts they made in the initial push to comply with Mr. Musk's directions. 'The DOGE Wall of Receipts provides the latest and most accurate information following a thorough assessment, which takes time,' Mr. Fields said. 'Updates to the DOGE savings page will continue to be made promptly, and departments and agencies will keep highlighting the massive savings DOGE is achieving.' Mr. Musk's group has listed more than 9,400 contracts it claims credit for canceling, for a total of $32 billion in savings. In all, Mr. Musk's group says it has saved taxpayers $165 billion. Compared with that output, Mr. Fields said, the reversals identified by The New York Times were 'very, very small potatoes.' He declined to say if contracts on the group's list beyond those that The Times found had also been revived. The Times uncovered those reversals by searching the Federal Procurement Data System, a government system that tracks changes to contracts. The Times looked for instances where contracts listed as canceled on Mr. Musk's website had shown signs of new life, such as having added funding, an extended timeline, or an update that included words like 'rescind' or 'reinstate.' That search turned up 44 of the cost-cutting group's zombies, contracts killed but then restored to life. That total may still be an undercount, because changes to contracts can take time to appear in the procurement data system, and because there is no standard way to identify a reinstated contract in this system. The Times's search may have missed some. The resurrections began in mid-February. Raquel Romero and her husband had a contract to offer leadership training to lawyers at the Agriculture Department. They lost it on Feb. 14, and gained it back four days later. That was a godsend for Ms. Romero and her husband, providing $45,000 in revenue at a time when all their other federal business had disappeared. 'We had lost all of the income that we were planning for calendar year 2025. We've had to sell our house. We're in the process of moving into a condo,' she said. 'We just feel really fortunate that we had this resource to buy some time.' The Agriculture Department said in a statement that it had restored this contract after discovering it was 'required by statute.' It declined to say which statute. Ms. Romero said she felt the reinstatement was the product of personal intervention, crediting a senior Agriculture Department lawyer who had been a major supporter of her and her husband's work. 'All I know is, she retired two weeks later,' Ms. Romero said. Other reversals began to follow. The Department of Veterans Affairs reinstated 16 contracts, the most of any agency in The Times's analysis. That department declined to comment about why. But veterans' groups noted that some of the canceled contracts involved functions required by law, such as a contractor who helped veterans search for military records to use as proof in obtaining benefits. That contract was restored after eight days. At the Education Department, Mr. Musk's group said it had saved $38 million over multiple years by canceling a contract to manage a repository of data about schools nationwide. But lawmakers and advocacy groups objected, saying that the law required that data to be collected, and that the government needed it to determine which schools qualified for certain grants, like some tailored for rural areas. 'They should have used a scalpel,' said Rachel Dinkes of the Knowledge Alliance, an association of education companies, including the one that lost this contract. 'But instead they went in with an ax and chopped it all down.' That grant was restored after 18 days, but with $17 million of its potential funding stripped away. The shortest-lived cancellation involved an E.P.A. contract, signed in 2023, to pay a Maryland-based company for help raising awareness about asthma. The E.P.A. canceled that contract at 4:31 p.m. on March 7, according to contracting data. Then it reinstated the contract — in effect, canceling the cancellation — at 6:58 p.m. the same night. Why? 'Any procurement that is reinstated reflects that the agency determined that funding action supported Administration priorities,' the E.P.A. said. The agency declined to give details about this case. Last month, the E.P.A. extended this contract for another year, agreeing to pay $171,000 more than before the cancellation. The contractor did not respond to questions. From the start of his group's work, Mr. Musk said the government would most likely have to undo some spending cuts. 'We need to act fast to stop wasting billions of dollars of taxpayer money,' Mr. Musk said on 'The Joe Rogan Experience' podcast in February. 'But if we make a mistake, we'll reverse it quickly.' But Mr. Musk also made a second promise, crucial to carrying out the first. He said his group would post the details of its work online to enable the public to have an accurate and up-to-date picture of what it had cut. 'We can name the specifics, line by line,' Mr. Musk said in the same interview. 'We've got the receipts. We post the receipts.' The Times has found numerous errors on the group's website since the beginning. Often, these errors inflated the value of the savings Mr. Musk's team had achieved. Mr. Musk promised the group could make $1 trillion in budget cuts this year, but so far it has fallen far short of that aim. And even those cuts have been inflated because of the inclusion of errors and guesswork. Mr. Musk's group, for instance, previously claimed credit for canceling programs that actually ended years or even decades ago. It also double-counted the same cancellations, and once posted a claim that confused 'billion' and 'million.' This month, The Times sent the White House a list of dozens of revived contracts that were still on the list. Two days later, Mr. Musk's group removed one: the E.P.A. contract that had been canceled for less than a day. But at the same time, it added five other already-revived contracts to its list of 'terminations,' claiming credit for $57 million more in savings that had already been rolled back.
