Forum Widgets
Latest Discussions
Help creating a matrix table
Good day wonderful people, I am trying to create a matrix table that shows correlations between types of issues people may be worrying about. The end result is to enable me to bring it into Power BI and utilize the chord visualization diagram to show where certain issues are connected and the strength of those connections. I have an excel sheet with a range (se below). The first column is a unique client ID. As the data may be collected over a period of time, the same client IDs may appear more than once (if they come back to the service provider). The data may include up to a maximum four issues that they are worrying about, and these are in adjacent columns to the client ID. As an example, in the below table, homelessness has low level interconnectivity with 'Priority Debt'; 'Disabled' & 'Mental Health' as they appear as issues alongside with that string; "Mental health" has a strong connection to "Non-Priority Debt" (and of course vice versa) with it occurring each time the other is mentioned. This is what I am aiming to show. Due to the way in which this data is laid out (from a database export), I am struggling to bring it into a matrix table (if that is the best method) and then on into Power BI. I've spent quite a bit of time in the forums, but can't find a way to apply the learning in them to the table output, or the right way to transform the data (via power query or otherwise) to help identify the interconnectivity. If anyone can point me in the right direction to solve this, I would be most grateful! Thank you in advance :-)124Views0likes7CommentsNew excel file cannot open because the file format or file extension is not valid
Hi. When I right-click on my desktop and create a new Excel file and try to open it, it gives the error "Excel cannot open the file 'New Microsoft Excel Worksheet.xlsx' because the file format or file extension is not valid" I have seen other threads and websites on this, like these two: https://www.stellarinfo.com/article/excel-cannot-open-the-file-because-the-extension-is-not-valid.php#article2 https://techcommunity.microsoft.com/t5/excel/excel-cannot-be-open-the-file-filename-xlsx-because-the-file/m-p/1504346 however, none of the methods helped me. I am not trying to recover any data just want to fix the issue. I can open other existing excel files and can even create a new workbook from the excel app when its opened but I want to know why this is happening and how to resolve it. Your help is appreciated. Thank you.fayaazFeb 14, 2025Copper Contributor5.1KViews2likes6CommentsIncorrect Name Syntax
Good Morning, I am new to Excel (watched one video in Training Session). Doing homework by using Excel Inventory Template. I have been copying and pasting to add to the number of cells in the Worksheet. I have 3 open worksheets. (They are all frozen) I attempted to highlight an entire column to remove the cell's contents and got an error above that cell column '@[Quantity in stock]]. After that, every time i tried to write in a cell or highlight, I got the dancing broken lines and in the formula bar the message '=[@[Unit p+H87:H111rice]]*[@[Quantity in stock]]' and the error message ' The syntax of this name isn't correct. Verify that the name: -Starts with a letter or underscore (_) -Doesn't include a space or character that isn't allowed -Doesn't conflict with an existing name in the workbook. I had nothing to do with the creation of the formulas. Microsoft Support asked me to cross reference the formulas with the original document but I don't have any idea how to. She then forwarded me here. I have over a hundred rows of information inputted in the columns and sincerely hope my situation can be remedied to save the day. Thank YouDiyahFeb 14, 2025Occasional Reader7Views0likes0CommentsCreate a Power query or Dax to find Open Status more than 1 hour
Hi Team, I have the door table Here I want to find that whether door is being open continuously(having open status & not closed) for more than 1 hour for each DeviceId. Data Description: The Status column shows whether door is open or Closed. CreatedOn column shows time duration. DeviceId column shows Deviceid. My first requirement is: We need to add the minutes(createdon) between each row for same deviceid where the status = open. But where a status = closed (if closed comes in between open) we reset the number of minutes. So we sum by the previous value where status = Open - Something like If current status = open and previous status = open then sum the time and do the below: Sum(previous value + new sum) Where/if status = closed set time count = 0 Sum(previous value + new sum) is going to take into account only the consecutive open status >60 min for same divice id whenever status is closed, this Sum(previous value + new sum) will be reset to/equal to 0 So on the first open status, the count will be 0. Then the next open status it will be time between 2 open statuses, then on the third it will be the time between two open statuses add the previous time.but any close status then time is reset. Could you please help me with a power query logic/dax logic? Second Requirement: This is only a sample data. But we have billions of rows of data in our dataflow that comes from SQL views. So, thought not to use DAX for this calculation in future. we can precalculate the maximum open state duration by preprocessing data (SQL, Power Query(dataflow), Spark, anything else...). So can you help me prepare data upfront. we need to solve the problem with a with a proper data model. for example, create a table with the longest open events upfront (in whatever technique you are comfortable with) Could you please help me to achieve this? PFA file here B&M.pbix Thanks in advance!Excellove15Feb 14, 2025Iron Contributor17Views0likes0CommentsDax to find open status for more than 1 hour
Hi Team, I have the below table: Here I want to find that whether door is being open for more than 1 hour or not. The Status column shows whether door is open or not. CreatedOn column shows time duration. Could you please help me create a dax logic to achieve this? PFA file here B&M.pbix Thanks in advance! SergeiBaklanSolved51Views0likes3CommentsImport Tab Delimited File from Clipboard
I'm looking to create a macro in excel to import a file from clipboard. I have tried to record a macro and that did not work. I do not want to enter a file name. I would like to copy from notepad and execute macro to import the data with pipe delimits. Sorry if this is a repeat, I ould not fine an answr anywhere. Thanks.JimKFeb 14, 2025Occasional Reader15Views0likes1CommentImpossible to sum numbers in Excel
I hope someone can help with this because I am pulling my hair out. When I try to sum numbers in a column, at the bottom of the page it gives me a count and not a sum. When I try to Autosum, it gives me =SUM(). When I try to add the specific cells, e.g. =SUM(E2:E15) it gives me zero. I've read through multiple forums which suggest to make sure the cells are formatted as numbers- which they are. Could someone help me out here because I am completely at a loss. Thanks.philip369Feb 14, 2025Copper Contributor152KViews0likes53CommentsAnalysis ToolPak Chi Squared test for independence
I recently had someone show me that through Analysis ToolPak, a chi-squared test can be performed in excel similar to other statistical programs such as SPSS. She just created the 2 x 2 contingency table and used data analysis to perform the function; however, after adding it into my personal computer (i have microsoft 365 Family), i cannot find the function under the data analysis section. Is there an easier way to do a chi squared test for independence that does not include working through all of the steps in excel or a good statistical add in which will perform the functions for me so I can work through the data analysis quickly.elirodriguez2015Feb 14, 2025Occasional Reader9Views0likes0CommentsWhat causes an excel file to 'Show Print Preview' rather than show the preview by default.
I am curious what causes this to happen, I have already set the print area etc., but for some reason I don't get an immediate preview when going to print, I have to click this button inside the print preview. (I have also attached the full screenshot Doesn't seem to matter what settings I change so far, just curious what exactly causes this change in excel, I have many other documents that just bring up the preview straight away when going to print, but at the moment, this one particular file I have to click another button. I have a MS flow that converts excel files to pdf automatically after a review process, but it keeps failing on this one file and I can't figure out why, but I think the key revolves around this, so I really want to find out what causes excel to present an additional button that you have to click to generate a preview rather than showing the preview off the bat like it does with every other file which works fine in the flow. ThankyouSolvedModulaFeb 14, 2025Brass Contributor67Views0likes5CommentsExcel help
Hi all, I have list of LGA location names in excel file. An example are as follows: Currently after I extract the data, I use look up function to get the New LGA column to replace the existing data. This is a manual task. The original file I have download from MS SQL and the file format is csv. Is there any other way that can do this more efficiently and automatically? Thankskpan8Feb 14, 2025Copper Contributor17Views0likes1Comment
Resources
Tags
- excel42,206 Topics
- Formulas and Functions24,466 Topics
- Macros and VBA6,353 Topics
- office 3655,939 Topics
- Excel on Mac2,619 Topics
- BI & Data Analysis2,335 Topics
- Excel for web1,879 Topics
- Formulas & Functions1,716 Topics
- Need Help1,703 Topics
- Charting1,609 Topics