Spalte mit Lücken
Hallo, ich habe eine Spalte mit Leistungswerten in einem Viertlstundenintervall für ein ganzes Jahr (ca. 35.000 Zeilen). Für die Weiterverarbeitung benötige ich aber Stundenwerte. Wenn ich vier Zellen markiere und die Formel aus dem zweiten Screenshot eingebem, kann ich diese problemlos nach unten ziehen. Wenn ich dann mit Strg+F alle leeren Zellen suche, markiere und lösche stürtzt mein Excel immer ab. Giebt es eine effizientere Variante die Stundenwerte zu berechnen und in einer Spalte auszugeben?Power Query by Default Excludes First Blank Column
I have researched this down the rabbit hole long enough without any answers. In Excel I created a Power Query Get Data from Sharepoint Folders using the Contents method. There are about 10 files where the data is in the same format, columns match sheets are all the same name, etc.... FYI, None of them are setup as Tables for reasons I won't go into. They just can't. When I import the files 5 of the files have data in column A while 5 do not. When I import the data the files that do not have data in column A Power Query is automatically removing those columns from those sheets. Which then screws up the column order when the data is appended and loaded to the table. How can I force Power Query to bring in Column A even if some files column A are blank?Edexcel solver help
Hello, I'm working on a scheduling model in Excel using Solver and I'm encountering issues with setting up the decision variables and constraints correctly. My workbook is divided into several sheets, and my goal is to maximize student satisfaction based on their subject preferences. Here’s an overview of my model: Students Sheet: Contains Student IDs and four columns for subject preferences (one per block). Additionally, I have helper columns (for example, F, G, H, I) that sum the number of assignments for each student per block. Classes Sheet: Lists all class offerings with columns for ClassID, Subject, Maximum Capacity, and a Block column. The Block column is a decision variable where Solver assigns each class to one of four blocks (integer values 1–4). Assignments Sheet: Features a grid where each row represents a student and each column (after StudentID) corresponds to a class (using ClassIDs from the Classes sheet). These cells are binary decision variables (0 or 1) indicating if a student is assigned to a particular class. Satisfaction Sheet: Uses formulas (typically SUMPRODUCT with IF statements) to check, for each student, whether they are assigned to at least one class in each block that matches their corresponding subject preference. It then calculates a “Total Satisfaction” value (for example, 1 if the student has a valid assignment in every block, 0 otherwise) and sums these to form the objective cell. What I'm Trying to Achieve: Objective: Maximize the total number of satisfied students (for example, the sum of satisfaction flags in cell I1 of the Satisfaction sheet). Decision Variables: The Block assignments for classes (for example, the range Classes!D2:D21). The binary assignment grid (for example, the range Assignments!B2:U101). Constraints to Implement: Block Assignment Constraint: Each class’s block must be an integer between 1 and 4. Binary Constraint: All cells in the assignments grid must be binary (0 or 1). Capacity Constraint: For each class, the total number of students assigned (using a helper cell that sums assignments per class) must not exceed the class's maximum capacity. Student Assignment per Block: For each student, helper cells (using SUMPRODUCT) calculate the number of assignments in each block; each must equal 1 (ensuring that every student gets one class per block). Issue Encountered: When I try to enter the “By Changing Variable Cells” in Solver, I get errors stating that my range reference is not valid—likely because I'm trying to specify noncontiguous ranges (Classes!D2:D21 and Assignments!B2:U101) in one entry. I'm not entirely sure how to correctly input these ranges and constraints in Solver's interface without causing errors. My Request: Could someone please provide detailed instructions or corrections on: The exact syntax for entering noncontiguous ranges (or a workaround) in the “By Changing Variable Cells” field. How to set up each constraint in the Solver dialog, including the correct references and options (for example, ensuring variables are marked as integer or binary). Any help or sample screenshots of how you set up a similar model in Excel Solver would be greatly appreciated!
Stockhistory returning #BLOCKED! error
I started getting this error a couple of days ago. Summary of the problem and what Excel Tech Support did but didn't fix the problem. 1. I get #BLOCKED! error with my stockhistory formula but stock data type formula is still working because I can still pull the stock price from Microsoft online repository (Bing) 2. In my local copy of Excel, the group data types has gone missing. I can retrieve it via customising ribbon but the icons for stock, currency, etc. is not there. 3. In the web version of Excel, the data types group is there but I still get the same error as (1) 4. Yesterday, the Microsoft support person reinstalled my copy of Office, checked the privacy settings in "Connected Experiences" but did not fix the problem. They said it was going to be escalated but I didn't get a call back at the agreed time. I went back to Tech Support via chat and the guy said that stockhistory function has been removed from Excel. Clearly clueless. 5. Is this a license problem?
"OVERWRITE EXISTING CELLS WITH NEW DATA..." GET DESELECTED
HELLO , I HAVE OFFICE 365 . FROM 1 MONTH AGO THE BUTTON IN DATA TAB THEN PROPERTIES AND IN IT "OVERWRITE EXISTING CELLS WITH NEW DATA..." GET DESELCTED AUTOMATICALLY AND WHEN WE REFRESH DATA IT GET OVERLAPPED . EARLIER IT WAS WORKING FINE LIKE IN SCREEN SHOT WE HAD SELECTED "OVERWRITE........" TAB BUT WHEN WE PRESS OK AND REOPEN IT IT GET DESELECTED PLEASE HELP ON THIS
Formula Help?!?
Hello all, I’m currently using the Index Match formula at the moment and it works with a work around but would like it find a better function as I’m sure there is one out there. At the moment I have a spreadsheet that shows a job list and then breaks down the information with the different columns,(timescales area, etc) so the index match works brilliant with the way I have the the spread sheet set up to show everything and each worker has a code that I put in and it sends the information to there display box on a different worksheet that’s easy to read. The issue I have is if I want two or more people on the same task I have to copy and paste the row for a many workers as I want on it, which then messes with some of the other formulas I have that counts the number of ongoing jobs and makes graphs and things…. So my question is - is there a function that does the same thing as index match but will allow for multiple entries within the cell so if the code it’s looking for is in there it will accept it, whether that be on a separate line or in “ “. So it accepts a cell currently with “00001” but can’t if the cell contains “00001, 00002” because I have two people working in the same task. Hope this makes sense and will appreciate any and all advice?
