Forum Widgets
Latest Discussions
Using Target Data from another Sheet to link to Power Query Data
Hi all, I'm building a training dashboard and have used Power Query to pull through the data that comes directly from the learning system. I can show how many units each person, and then each department has completed. However, Different Departments, and then divisions within those departments have different mandatory competencies. I have these set up in another sheet in my workbook as a course database. I've set it up for Course - Tier (Mandatory or not) - Department - Division. So this table shows how many mandatory units each department must complete. My issue when trying to create relationships in that there are duplicates, as some departments have a number of the same courses. Everything else is working ok - but I need to show how many courses have been completed vs the amount of courses they need to complete each year, and in case this changes over the year (for instance if we add a new course) I want that to be dynamic instead of just saying "out of 18" for instance. Can anyone provide any guidance? I'd greatly appreciate it!
How do I weigh ranking based on value size
Hi All Im trying to rank some numbers in excel and it works fine so far. However it is not depicting differences between values as i would like: Ranked table is on top with the following formula: =RANK(C17;$C$17:$C$23;1) / =RANK(D17;$D$17:$D$23;1) onboardings ranking 7 6 5 4 1 3 2 onboardings data 1013 248 222 139 14 51 46 Now as you can see it ranks just fine, but the difference between the highest in onboardings ranking (7) and the next highest (6) is just marginal. I would like the formula to be able to give some space in between, maybe say more like 8 or 9 and skip in between. How can i define the Rank starting at 10 and be variable in between? Thanks for the help
Excel 2021 Save As from 3rd Party Toolset
I have a 3D CAD "SolidWorks" that allows me to save out a bill of material. I has xlsx and xls as options. The issue is that Excel uses general for the cell format which changes some numbers to dates and others like -001 to -1. I have a sheet template and workbook template with the cells set as Text but since it doesn't open excel I cannot get it exported to that template. Is there a way to overcome the default from Excel? I don't see a way to even temporarily change the template as I can only find the custom sheet and workbook template I created. I asked the SolidWorks customer support VAR and SolidWorks doesn't allow me to control this or point to a specific template. Note: if I open the file with excel it changes my data and I have to manually repair it after changing the file type.Replacing Value Based on Two Conditions in Power Query
I have a list of people who have access to a certain Site Collection and Subsite. I am trying to replace a value in the Subsite column using an If-Else statement in Power Query to only replace one value if it meets the criteria. Specifically, if the Site Collection is equal to "en_ca" and the Subsite is equal to "D26 Plumbing", I want to replace the Subsite text with "D26 Plumbing and Bath". I've tried a few different equations but I either replace everything in the list with "D26 Plumbing and Bath" or I get an error. = Table.ReplaceValue(#"Filtered Rows", each [Subsite], each if [SiteCollection]="en_ca" and [Subsite]<>"D26 Plumbing" then [Subsite] else "D26 Plumbing & Bath", Replacer.ReplaceValue,{"Subsite"}) What am I missing here?Adding values generated by IF Function
Hi all trying to set up a QA spreadsheet with Yes/No answers. I used the =IF(B9="Yes"; "1";"0") To generate values in Column C I want to add up those values in column C to get a total so I used =SUM(C9:C41) but it's not generating the total. I also tried =SUM(IF(B2:B14="Yes"; 1;0)) which worked but only added up a few of the values in column C What can I do to fix this?Calculate same period in Excel
Hi everyone, I am trying to use excel to achieve the following but not sure if it something possible. For some reasons, I need it to be in excel. I have a sale sheet with customer's sales and visit. Each Customers has a start and end membership period. I wanted to calculate the sales & visit made by this customer within their current period. For example, Customer A start date is in June 24 and will end May 25. Since now is March, it will calculate the total sale or visit made between June 24 till Feb 25 and compare it with same period last year (Jun23 to May 24) and then calculate the percent change. I tried using some formula, but the calculation is wrong. For example, Customer A Visit from June24 to Feb25 should be 24 but excel answer is 22. Excel_Sales.xlsx Also is it possible to display the monthly breakdown table ie. June24 to Feb25 and June 23 to Feb24? Would appreciate any advice. Thanks so much in advance.
CountA, Unique & Filter Combined Formula Query
I'm using following formula to count unique values if criteria in 2 different columns meet. It is working fine and counts unique value when both critieria meet. However, if one column criteria doesn't meet. It returns 1 value instead of 0. =COUNTA(UNIQUE(FILTER(B:B,(A:A=H3)*(C:C=J1)))) Forexample in following picture, for programme adbfeg there is no part number on supply route 2159A, it is still returning value as 1 instead of 0 in column L. Could you please help me in finding out what's the error? Also, can you please guide how can I calculate using same formula for blank supply routes in column M. I'm not sure how to attach the file in this post as I'm new to community. If someone can please guide me, I'll attach the file as well.
