Need help creating a dynamic graph from data extracted from a pivot table
Hi experts, I have hourly data collected from our shared solar system (14 lots). I can get that data into an excel table easily, then use a pivot table to get it summarised by Date-Month.Day (rather than by hour) and Lot. A calculated column in the pivot table gives the percentage ratio of the solar power delivered each day to each lot. [Sidenote: The solar power is not delivered equally every day, but is demand based with an overall objective of eventually sharing the power equally, where equally depends on the strata lot allocations, so some lots get a different percentage than others. Furthermore, the distribution is split into 3 phases, where a given set of 4 or 5 lots share the same phase] I've added slicers to the resulting pivot so I can look at each month of data for each phase. [Note that the system went into operation on Nov 22, so the November data is only a few days, beginning Nov 22] What I'm trying to achieve is to get the data graphed to show the Ratio of Solar Delivered per day per Lot. Something like this, which is fine for Phase 1 for the month of November only: To create this graph, I used array formulas in some spare cells in the pivot table to tabulate the data like this: The table extends dynamically as I add months and/or phases to the pivot table display - which is great. Just what I wanted. BUT... the graph stays stuck on showing just the first four lots and the first 9 days because that was the size of the table when I grated the graph. I WANT THE GRAPH TO EXPAND DYNAMICALLY AS THE TABLE EXPANDS I've tried changing the Chart data range to accommodate the extra data, but if I then change back to a smaller set of data, the graph size does not change. viz- below is how the graph looks after changing the Chart data range to accommodate some extra data, then reduced to the original data set: I WANT THE GRAPH TO CONTRACT DYNAMICALLY AS THE TABLE CONTRACTS In other words, when I change the slicers to show the original data set, I want the graph to return to its original format ~------------------------------------------------------------------------------------~ I've read posts that talk about formatting your data as a table. Bit if I try and format by "helper" data as a table, I get the following warning: If I exclude the calculated headings, I get #SPILL errors ------------------------------------------------------------------------------------ I'm at a loss to work out how to create a dynamic graph. I'm hoping someone in the community can help - good luck and happy new year. And thanks for taking the effort to read this rather long post. If I can figure out how to add my source file to this post, I'll add it. In the meantime, you can view/download my source file here: https://1drv.ms/x/c/c95331b296c5ed04/IQCxxcpJWbyOTIXiDxyvmg9mAS5xcAADjTrP0JXBbs1IHBI?e=JJvVYH RedNectar
when I try to print from Excel I often get printing error
I usually get this error daily, often if I have more than one excel open at a time. Most of the time I can save what i am working on and close it. Reopen the excel file and it will print fine. But when I am in a crunch it is annoying that I cannot just print that sheet and move on. I can print fine from any other program/software I am running. Using Windows 11 Enterprise Microsoft 365 for Enterprise not sure what else you need, everything else prints fine.
Simplifying cost calculation using array instead of IF statement
Hello, I am in the process of calculating the cost of refining precious metals based on user input of specific parameters. For example, if a certain dore intake of Silver has 90% Silver (Ag) content then lookup the specific processes and multiply the cost per oz with the intake ounces. I have attempted to combine IFS and Xlookup for each process separately but the formula looks very unwieldy. I am also enclosing a slightly simpler formula of IFS and sum where the total cost is calculated in one cell (Q12). Here is the link: https://docs.google.com/spreadsheets/d/1hizmF6EwhxOPEeR10bXJsBOKeOtXude8/edit?usp=drive_link&ouid=103354753371375324640&rtpof=true&sd=true I am looking to see if I can have a more dynamic iteration of the formula in Cell Q12 as well as in the calculation of the individual processes in Row 4 , Cols P:V. Thank you. Regards, Shams.
pivot table
In recent versions of Excel 365 for Mac, the drag-and-drop behavior in PivotTables has regressed significantly compared to previous releases. Specifically, when attempting to move a field from the Row or Column area into the Filter area, Excel often interprets the action as a removal rather than a relocation. This breaks the intuitive manipulation of Pivot layouts that has been standard for years. Additional regressions include: Reduced responsiveness of the Field List pane Inconsistent behavior between older PivotTables (created in previous versions) and new ones Hidden or unavailable “Classic PivotTable Layout” options Lack of visual feedback when dragging fields between areas Increased reliance on context menus for basic layout changes Incoherence between Mac and Windows versions of Excel These changes hinder productivity for advanced users who rely on fast, flexible layout adjustments. Suggested improvements: Restore or make optional the classic drag-and-drop behavior Ensure consistent handling of field movements across all areas (Filter, Row, Column, Data) Improve visual cues and drop zones during field manipulation Guarantee parity between Mac and Windows versions Excel’s PivotTable interface used to be a model of intuitive design. Please consider restoring that flexibility — especially for users who build and modify complex reports daily.
Funktionen werden nicht angezeigt
Hallo. Ich habe das Problem, dass manche Funktionen nicht für die Auswahl angezeigt werden (funktionieren tun sie aber). D.h. wenn ich zB in der Zelle anfange zu schreiben =xverweis.... erscheint die Syntax-Info nicht, auch über die fx zum Einfügen werden die Funktionen nicht gefunden/angezeigt. Woran kann das liegen, was kann ich da machen? Aktuell sind es xverweis und datedif, die ich vermisse. und es sind sicher noch weitere. Ich habe es mit zwei verschiedenen Excel 365 Abos versucht, bei beiden dasselbe Problem. Danke für Tipps.Hi, I need help. I'm creating a calendar, based on events at our farm, which are on different dates.
Each event has its own column, the name of the event is at the top of the column, and the different dates it will occur are listed underneath it. I need to get this event name to automatically appear on an interactive calendar I made in the next sheet. (The calendar shows the date and weekday of a certain month in a certain year, you can change the month and year to whenever you want), I've tried the xlookup functions but I can't seem to get it working. Please help if you can! I'd be happy to take advice.
Calculating hours using pivot table
Hi, I'm making a personell planning sheet and I want to calculate the sum of hours teachers give lessons. I have 2 tables and my data is formatted like this (simplified) Lesson name Hours Teacher 1 Teacher 2 Lesson 1 2 Paul Lesson 2 3 Pete Lesson 3 2 Paul Pete Teacher name Max working hours Paul 10 Pete 15 Now I want to create an overview of how many hours each teacher is teaching using a pivot table. This is easy when there is just one Teachter collumn but I need to calculate the sum using both teacher collumns... I need to overview to be something like this: Paul -> Lesson 1 + Lesson 3 = 4 hours Pete -> Lesson 2 + lessen 3 = 5 hours Then the next step is to use a metric or KPI to calculate if each teacher is exceeding their max working hours... Can anybody hlelp me with these problems? Thanks!Different functions for each column in a subtotal
ive got a sheet where i want to subtotal using different functions in different columns. i will always have different data every time i want this to execute, so cant hardwire the row range, also each group (based on date) will have different number of rows. Eg, on change of date, sum column B, count column C, max column D. I hope i explained the problem adequately.
