Pinned Posts
Forum Widgets
Latest Discussions
Best way to get rolling 12-month totals
I am using Excel for Mac version 16.102. I have data in a Table which is basically date, category & value. There are many other columns I use for filtering. I pivot this data to get totals by month within year but I hide the yearly totals and just have the grand total. My pivot table is filtered to include\remove high-level category data. I have a Timeline which allows me to select the date range of my pivot. I have a number of slicers to facilitate filtering to get the dataset totals I need. There maybe a better way but the above works. My ask is how do I go from the above to easily get a rolling 12-month totals for say Jan 24 - Dec 24, Feb 24 - Jan 25, Mar 24 - Feb 25 etc etc? I can move the timeline which gives me the correct answer but I'd like to be able to see the rolling totals for the last 12-months at a glance without having to keep moving the timeline. Eventually I'd like to graph the last 12-months so I can see movement over time. Can I do want I want with a pivotable or do I need another approach? Thank you for your help.
SUMIF help
Trying to create a total based on 2 pivot tables. What formula do I put in the column in red text. PIVOT #1 ITEM SUM APPLES 5.2 PEARS 3.2 CHERRY 0.4 TOAST 0.2 ORANGE 1.8 WATER 0.4 LIME 2.5 PIVOT#2 ITEM: SUM PEARS 1.8 BERRY 0.7 PEACH 1.4 KIWI 1.4 WHAT FORMULA DO YOU PUT IN SO THAT IT WILL SEARCH MATCHING 1ST COLUMN AND ADD TOTALS FROM BOTH PIVOTS TO THIS 3RD LIST. ITEM: TOTAL (PIVOT #1 AND #2) THAT MATCH ITEM APPLES PEARS CHERRY TOAST ORANGE WATER MUSHROOM KIWI
SEQUENCE formula with curly brackets
I have this simple formula, it works as expected up until the SEQUENCE part : In short rws would equal to 32, and srt to 1 I expected this to output the same as SEQUENCE(32,1,1,1), but it outputs just a single 1 I did check the evaluation process of the formula and it seems like this instead makes SEQUENCE({32},1,{1},1), and that, in a new cell does outputs the same single 1 What is it that i'm missing? Is it because of the LET function? Is there any fix to this? Thank you in advance! =LET( x,E1:E2288, y,SEQUENCE(ROWS(x)), z,FILTER(y,x=""), str,VALUE(INDEX(z,G1)), rws,VALUE(INDEX(z,G1+1)-str), SEQUENCE(rws,1,str,1) )
max corresponding to a value which may be in 1 or multiple sets
Hi G6 is showing error because F6 is not found in D15:D50. what would be the simple process that F6 is searched in D15:D50 and D56:D91 and returns value from col K instead of an error whether or not that value exist in either of the sets.Rack of Lambda
There’s been a lot of content shared recently to commemorate the upcoming 40th anniversary of Microsoft Excel. Personally, I’ve only been using Excel for around half that time, but October also marks my 2-year anniversary since "joining the conversation" on this forum. As a gift from me to you (anyone interested), I’ve spent some time over the past few weeks revisiting old posts, updating methods I shared previously and packaging them into a collection of generalized Lambda functions to assist with a variety of common array manipulation and transformation scenarios. The attached file contains some 35+ Lambda functions, ranging from very simple concepts to much more advanced techniques. You can also import them directly from my gist, if desired. While they were all compiled and composed of my own accord, I would be remiss if I failed to credit the community and its members as a major resource in my own development. The amount of knowledge, tips and tricks gained through community collaboration is simply invaluable. You may notice some recurring themes in the way I’ve written many of the functions. For example, I like to keep the optional arguments as simple as possible, using either Boolean values passed to IF, or numeric options from 0 to 3 passed to CHOOSE. Also, many of the array transformation functions use TOCOL-IF-SEQUENCE in one way or another, with MOD-QUOTIENT-SEQUENCE used only a few times in the more complex algorithms (e.g. HWRAP and VWRAP). The collection also includes a few examples of Lambda recursion, the most notable being PF (Prime Factorization). CROSSJOINM was written as a "how-to" demonstration for filtering multiple optional arguments using LAMBDA and NOT-ISOMITTED. There’re also some powerful scanning functions like SCAN3, which can handle multiple input arrays, as well as EVALS with VALS2 to VALS7, which can store and recall multiple variables at each iteration (useful for corkscrew calculations). What you won’t find, however, are methods that use INDEX in an iterative manner with functions like MAKEARRAY, SCAN, etc. as these are only efficient when iterating over a range reference (they will bog down considerably and become practically unusable after just a few thousand iterations when looping over an array object). As such, I don’t recommend them as "generalized" solutions, although they can be very effective on a case-by-case basis. Similarly, you will only find 2 examples in this collection that use REDUCE-STACK in a limited capacity, with fewer than 10 iterations, as I also consider this to be a method of last resort due to its problems with efficiency when the number of iterations cannot be controlled. Hopefully one or two of them proves useful. If not, no big deal. Many of the examples in the attached file are interactive, so you can see how the different options affect the output. For those brave enough, please feel free to share your own custom functions too. I’d love to see what you got. Cheers!
Camera tool overlapping images
Heya, I'm having issues with the Camera tool layering the same image ontop of itself frequently, especially when re-opening the file whereby it states: This workbook contains links to one or more external sources that could be unsafe. If you trust the links, update them to get the latest data. Otherwise, you can keep working on the data you have. If I select update, it will layer a new picture on top with source formatting. If I select don't update, it will layer with current formatting. It also layers intermittently while working on the workbook, unsure if this is from a specific action... Please help!
Stacking The Beatles in Excel: An Exercise in 3D stacking
The setup: You're provided with an Excel workbook with 12 sheets for 12 Beatles albums. All 12 sheets have the same fields, but the fields are not in the same order in each sheet. The goal: Roll up 12 sheets and order the columns uniformly in the array. I'll step you through my solution and then open up the discussion for anyone that would like to give this a try. First, a named item for the 3D reference: =PleasePleaseMe:LetItBe!$A$1:$F$31 Next, HSTACK and SORT gathers the track listings: =SORT(HSTACK(Albums3D),,,1) This is a snip of what the stack looks like: The biggest step is the re-stack: =LET(AlbumsNoHeader,DROP(StackedAlbums,1),stack,HSTACK(TOCOL(TAKE(AlbumsNoHeader,,12),,1),TOCOL(CHOOSECOLS(AlbumsNoHeader,SEQUENCE(,12,13,1)),,1),TOCOL(CHOOSECOLS(AlbumsNoHeader,SEQUENCE(,12,25,1)),,1),TOCOL(CHOOSECOLS(AlbumsNoHeader,SEQUENCE(,12,37,1)),,1),TOCOL(CHOOSECOLS(AlbumsNoHeader,SEQUENCE(,12,49,1)),,1),TOCOL(TAKE(AlbumsNoHeader,,-12),,1)),UNIQUE(stack,,1)) Finally, putting it all together: =VSTACK(UNIQUE(TAKE(StackedAlbums,1),1),ReStack) I'm most interested in the "restacking". It seems heaven and earth are being moved to sort things out and wonder if there's a more direct way of doing it. Formulas only. Please no VBA or PowerQuery.Profit/Loss per Day Calendar
Hi All, I am looking for formula for these 2 items. 1. Want total Profit/Loss per date in Calendar (E.g. Total Profit on 02 Feb 2022 was -90.65). 2. If Profit Cell to be Green, If Loss cell to be Red Link to Excel Sheet. https://1drv.ms/x/s!AlvQCyQGg78NgkNEsm2PYzAfJIEo?e=bF7ctc
