Forum Widgets
Latest Discussions
Countifs a selected month is between date range
Hi all I may be getting confused with this one, but essentially I have the following table: I am trying to use COUNTIFS to count how many projects from the table are between the date range (start month and end month) I know how to do this the other way round, e.g. provide a date range and lookup the table. However not sure how to do it in reverse. Can anyone advise? Thanks Mattmatt0020190Feb 17, 2025Brass Contributor22Views0likes2CommentsCreating Logic to find whether columns in one table matches other columns in other table
Hi Team, I have 2 tables: Door table as below: Store Status table as below: They are modelled as below (let me know if we can better model it-suggestions are welcomed): Now, I need to create a logic(Breach) to find when Door Table - Status column being Open when Store Status table - Status column being Closed (indicated by - 'C'). During this logic, we have to make sure it satisfies below conditions: DateTime column of Store Status table should match the createdon column of Door table Store id column of Store Status table should match the siteid of Door table Output should return 1 if the conditions meets else 0. Could you please help me create a logic for this? PFA file here B&M.pbix Thanks in advance! SergeiBaklanExcellove15Feb 17, 2025Iron Contributor24Views0likes0CommentsCreate 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!SolvedExcellove15Feb 17, 2025Iron Contributor161Views0likes9CommentsAnalysis 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 17, 2025Copper Contributor27Views0likes1CommentPreventing the user from going to the next page in UserFrom
Hello , like the title says I have a problem in the UserForm.When the fields are blank in the first page(employee) then if the user clicks on the second page (training) , the message should pop-up and the user should stay on the first page.However, it looks like it stays on the first page but the controls(fields) of the second page (training) are visible , so only the page tab reverts back as employee but the fields are from the training page.Where is the mistake ? I would appreciate the answer ! Screenshots : Before clicking the training(2.page) when all fields are empty: after clicking training page: after closing the message : The code: Private Sub MultiPage1_Change() Dim isValid As Boolean isValid = True ' When trying to navigate to Page 2 (Training), validate fields first If MultiPage1.Value = 1 Then isValid = ValidateFields() If Not isValid Then MsgBox "Please complete all employee fields before proceeding to the Training page." MultiPage1.Value = 0 End If ' Load the training list now that we're accessing Page 2 Call LoadTrainingList("") End If End Sub Private Function ValidateFields() As Boolean ' Initially assume all fields are valid ValidateFields = True ' Check name field If Me.txtName.Value = "" Then Me.txtName.BackColor = RGB(255, 200, 200) ' Light red for missing data ValidateFields = False Else Me.txtName.BackColor = RGB(255, 255, 255) ' Reset back color if correct End If ' Check role field If Me.txtRole.Value = "" Then Me.txtRole.BackColor = RGB(255, 200, 200) ValidateFields = False Else Me.txtRole.BackColor = RGB(255, 255, 255) End If ' Check ID field If Me.txtID.Value = "" Then Me.txtID.BackColor = RGB(255, 200, 200) ValidateFields = False Else Me.txtID.BackColor = RGB(255, 255, 255) End If ' Check section field If Me.txtSection.Value = "" Then Me.txtSection.BackColor = RGB(255, 200, 200) ValidateFields = False Else Me.txtSection.BackColor = RGB(255, 255, 255) End If End Function Private Sub UserForm_Initialize() ' Set the initial page to Employee (Page1, assuming index 0) MultiPage1.Value = 0 ' Ensure all relevant controls are reset or visible/invisible as needed ResetFieldColors ' Start/resetting particular fields or visibility information here if required End SubElla123Feb 17, 2025Copper Contributor33Views0likes3CommentsMacro not working
Good morning - My macro isn't working and I need some help. I reached out here a bit ago and someone created this macro to help updates/made this format a bit more PowerBI friendly. I have my original report in the OR Report Scrubbed 2019-2024 file, then I head to the 2016-2023 OR Data.PowerBI format file and update the link in the PARAM tab, and then refresh the table. I was wondering if you could help fix this macro/table so it will refresh with the most recent data! This is the error that I'm getting:nattiej101Feb 17, 2025Brass Contributor693Views0likes4CommentsExcel 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 17, 2025Copper Contributor69Views0likes3Comments#SPILL Error in Excel
I have been working on an assignment and using the XLOOKUP function to try to get a result. No matter how many times I do it, I keep getting the same answer. I have re-entered in the information countless times and can just not figure it out. I have also tried the VLOOKUP function. It says my spill range is not blank but I have no options to select obstructing cells or anything. I have the 365 Excel version. Any help?70Views0likes2CommentsStamp Duty Calculation
Hello, I have a SUMPRODUCT formula to calculate stamp duty in the UK below where F2 = the purchase price of a property. =SUMPRODUCT(--(F2>{0;250000;925000;1500000}),(F2-{0;250000;925000;1500000}), {0.03;0.05;0.05;0.02}) I want to edit this formula so that if F2 is less than 40000 then the stamp duty = £0 regardless of the above formula. Can anyone assist me please? Thanks, PatSolvedPat_Burrows1970Feb 15, 2025Copper Contributor24KViews0likes10CommentsImport 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.SolvedJimKFeb 15, 2025Copper Contributor58Views0likes4Comments
Resources
Tags
- excel42,213 Topics
- Formulas and Functions24,469 Topics
- Macros and VBA6,357 Topics
- office 3655,940 Topics
- Excel on Mac2,620 Topics
- BI & Data Analysis2,336 Topics
- Excel for web1,881 Topics
- Formulas & Functions1,716 Topics
- Need Help1,703 Topics
- Charting1,609 Topics