Using mode with several criteria
Hello everyone! I'm currently treating some data before I send it to do some machine learning. I have the following data: id Ano mês data_inversa dia_semana horario uf br km municipio causa_acidente tipo_acidente classificacao_acidente fase_dia sentido_via condicao_metereologica tipo_pista tracado_via uso_solo ano pessoas mortos feridos_leves feridos_graves ilesos ignorados feridos veiculos And I created a new table to summarize this data for months instead of accidents, here are the columns: BR KM Ano Mês Clima (Moda) Tipo Pista Acidentes Veículos Ilesos Feridos Leves Feridos Graves Mortes Causa (Moda) Risco What I want to do is calculate the mode of condicao_metereologica for each combination of BR, KM, Ano and Mês. But I can't wrap my head around on how I could do this. Any help is appreciated! Let me know if I made anything not as clear as it is in my head xD
Data Reconciliation Assistance Needed – Time Range & Sum Matching
I have two sets of data that need to be reconciled. Specifically, I’m trying to identify which combinations of numbers from these datasets can sum up to a specific target value. Additionally, the reconciliation should only consider entries that fall within a defined time range—from approximately 8:00 AM on one day to 10:00 PM the following day. I’ve tried using Solver and Goal Seek, but the results don’t seem accurate. I also attempted using Microsoft Copilot, but the outcome still appears incorrect. Could you assist with identifying or generating the correct combinations based on the criteria above?Welcome to the Excel Community
The Excel Community is a place we've built for all of you. You can learn more about how to do something with Excel, discuss your work, and connect with experts that build and use the product. With over half a billion Excel customers, we want to engage with you in fundamentally different ways and the community is a starting point for that. Our community helps answer your product questions with responses from other knowledgeable community members. We love hearing feedback and feature requests from you which helps us build the best version of Excel ever. If you have found an outage or a bug please post at our Answers forum. We look forward to getting to know you! Sangeeta Mudnal & Olaf Hubel on behalf of the Excel Team
Can't print this document! Print preview not matching Page Layout or Page Break Preview
I'm trying to "print" this to PDF, but can't get all my tables and graphs to fit nicely when printing This is my view in Page Break Preview This is my view in Page Layout This is the Print Preview screen, I think when it's scaling the rows to fit on one page, it's not scaling the graphs as well so they keep moving around. How do I make this look nice when I print? I have 16 tables with graphs plus a footer, so they aren't going to fit all on one page, that's why I set it to print over 4 pages.
name manager rejecting lambdas
I tried pasting a lambda in name manager but excel refused. I belive that it's because either it is too long (which it isnt) or it has too many optional parameters (8). Anyone knows why name manager will reject to paste my lambda? These are the parameters: =LAMBDA(array,slice1,[index1],[return_index_slice2],[index2],[return_index],[if_not_found],[logic],[headers],[function],LET(...))
New in Excel for the web: Power Query Refresh & Data Source Settings for authenticated data sources
We’ve reached yet another milestone in Excel for the web: Power Query Refresh is now generally available for queries sourcing data from selected authenticated data sources. As we released the ability to refresh Power Query data from anonymous data sources (link), it was only a matter of time until we added the ability to refresh Power Query data from authenticated data sources, which are the majority of data sources used, and require users to enter credentials. This milestone also enables us to release Import with Copilot to Excel for the Web (following Win32 and Mac), as it relies on Power Query for refreshing data. Getting started These new functionalities are available to all users on Excel for the Web. See this support article for more information on Power Query data sources in Excel versions. efresh a data source in Excel for the web using Power Query Refreshing Power Query queries You can now refresh the Power Query queries in your workbook that source data from a selection of authenticated data sources: Select the Data tab > then choose Refresh All Open the Queries Pane > then select Refresh When you refresh a query, if authentication is needed, you can select the relevant method – anonymous, user and password, or your organizational account. For example, to refresh organizational data, select the respective method: Your user will be automatically identified (you can also switch it, if needed), so you can easily click “Connect” to continue the refresh process. The list of supported connectors includes: SharePoint* files (Excel workbooks, TXT, CSV, XML, JSON, PDF) SharePoint* folders SharePoint Online List SharePoint List SQL Server Database OData Feed Web API IBM Db2 Database PostgreSQL Database Azure SQL Database Azure Synapse Analytics Azure HDInsight (HDFS) Azure Blob Azure Table Azure Data Lake Storage Gen 1 Azure Data Lake Storage Gen 2 Azure Data Explorer Dataflows Dataverse Microsoft Exchange Online Dynamics 365 (Online) Salesforce Objects Salesforce Reports *SharePoint/OneDrive for work or school The refresh happens behind the scenes so you can keep editing the workbook while refreshing. Note: There is a limit for 1000 data source credentials. For example, if you connect to the same data source with 2 different users, it counts as 2.. Managing queries using Data Source Settings You can now view and manage data source credentials for the Power Query queries in your workbook using Data Source Settings: Select the Data tab > then choose 'Data Source Settings’. Choose between ‘Current Workbook’ and ‘Global Permissions’ to view and manage data sources credentials in the current workbook or across all workbooks, respectively. To delete the credentials stored for a data source, click on the ‘Delete’ button. To edit the credentials stored for a data source, click on the ‘Edit credentials’ button. In addition, we’re introducing a new functionality in Data Source Settings – authenticating to a data source that exists in the workbook from within the dialog: Select the Data tab > then choose ‘Data Source Settings’. Navigate to ‘Current Workbook’. Click on the ‘Add credentials’ button: What’s next? Future plans include releasing the full Power Query Editor experience to Excel for the Web. Feedback We hope you like this new addition to Excel and we’d love to hear what you think about it! Let us know by using the Feedback button in the top right corner in Excel - add #PowerQuery in your feedback so that we can find it easily. Want to know more about Excel for the web? See What's new in Excel for the web and subscribe to our Excel Blog to get the latest updates. Stay connected with us and other Excel fans around the world – join our Excel Community and follow us on Twitter. Jonathan Kahati, Gal Zivoni ~ Excel TeamFind average with checkboxes
Hello, I’m not fluent in Excel but I do alright. Currently I am having a real hard time figuring this out. I’m trying to pull averages from different cells to one cell but based on if a box is checked. Backstory: I track times in a task and get the averages for each person but also the store average. As some people are no longer in those roles or now doing part time, I want the store average to only show the data from the people I choose. Ex: cell I3 is the store average. I only want it to account for the data in cell C11 if the checkbox in D10 is checked. I only want it to account for the data in cell G11 if the checkbox in cell H10 is checked. And so on and so forth. I have tried: =IF(D10,C11,IF(H10,G11)) =AVERAGEIF(D10,”TRUE”,C11(H10,”TRUE”,G11)) =AVERAGEIF(D10,”TRUE”’C11)&AVERAGEIF(H10’TRUE”,G11) but none of those have worked.
Using a combination of IF Statement and LET function
Hello, I am trying to do the following from the attached link: If the Fiscal period date in Col B (of the SORTED_INV_CN worksheet) is greater than/equal to Dec 2025 or less than April 2025 then type in "Ignore" Else, use the LET function to lookup by Invoice# in the LookUp-Comment worksheet However, the function in Cell D2 of the SORTED_INV_CN worksheet is returning "Ignore" for all the rows in Column D. At the end of the day, I am required to evaluate the xlookup function for date range less than Dec 2025 and April 2025. I am partial to the LET function as it reduces the need to add a 2nd xlookup formula. The LET function works without the parent IF function but is returning erroneous data as soon as I incorporate the IF(OR( function. Here is the link to the file: https://docs.google.com/spreadsheets/d/1oiWWXXOSorZuB5Q4vzgdHO_kyRkvGE3L/edit?usp=drive_link&ouid=103354753371375324640&rtpof=true&sd=true I think the problem lies in the way the date format is being interpreted between the evaluation cells. Hope you can point me in the right direction. Thank you. Regards, Shams.
