Forum Widgets
Latest Discussions
Missing slots calculation
Dear Experts, I have data like below:- Each Frame(Column A) has 4 corresponding slots(Column B) :- So, A6(955) has entry 3,8,13,18 , which is a complete set, and so on as shown in the ok(complete set) example. In column J, K , I want to populate the Frames(J == SFN == Frames) which are in complete and in Column K, want to populate the slots which are missing in those corresponding Frames. Attached is the Worksheet. Thanks in Advance, Br, AnupamSolved63Views0likes7CommentsAutomatic Rolling 12 Month List
Hi all I've looked first but nothing seems to give me what I am looking for. Hope you can help! I'm simply looking for a rolling 12 months list that automatically updates based on the current date. The financial year starts in March. An example below based on today's date being in February 2025: Column A January 26 February 25 March 25 April 25 May 25 June 25 July 25 August 25 September 25 October 25 November 25 December 25 The idea being that once the month has passed, February 25 would become February 26 in the list and so on. Can this be done with formula based on the today() date? Thank you for any helpSolvedmatt0020190Feb 08, 2025Brass Contributor58Views0likes5CommentsExcel Online Checkbox COUNTIF Issue – Checkbox State Resets Unexpectedly
Hi everyone, I'm experiencing an issue with checkboxes in Excel Online. I have a table where I use checkboxes (TRUE/FALSE) in five rows, and below them, I use the following formula to count how many checkboxes are checked: =COUNTIF(F1:F5,TRUE) However, when I check a box, the counter briefly updates (e.g., from 0 to 1 if only one box is checked) but then immediately resets back to 0 within a second, as if the checkbox is being unchecked in the background. This behavior does not occur in the offline (desktop) version of Excel, where everything works as expected. Additionally, after reloading the page, sometimes the checkboxes reset to an unchecked state, and other times the counter updates correctly. Is there a known issue with checkboxes in Excel Online, or is there a setting I might have misconfigured? Any advice would be greatly appreciated! Thanks in advance.MichalPavFeb 08, 2025Occasional Reader18Views0likes2CommentsFormula Error making my head explode!
I have a pretty intricate spreadsheet tracking staffing and OT usage and ONE line has an error code that is throwing off the whole thing! I am banging my head against the desk because I CANNOT find the error. The actual formula is correct and pulling the right answer, but still giving me the error notification in the top of the cell. This is causing the next step in the spreadsheet to also show an error. Anyone that can help a girl out?!?!?kaylatimmsFeb 08, 2025Occasional Reader23Views0likes2CommentsExcel SUMIFS(): Calculate sum in point of time of negative balance components only.
Hi community, I'm not an Excel expert, so I get confused when trying to accomplish even the most trivial calculation. And for this one, I've already spent too much time trying and searching online, without success. Even three different AI engines didn't give me clear answers. It seems like no one has ever stumbled upon this scenario, strange. So, this is my sample dataset: Please note my dataset uses "date smart IDs" instead of "date" datatypes, but nothing changes, once you know that Date_2_SID = -1 defaults to 1900-01-01 in the corresponding date format. Now, suppose we need to calculate the Total Overall Balance (which is the SUM of each row Value 1 - Value 2) at my reporting date which is 2022-12-31. Result: Then, suppose we need to add an additional metric to show the Total Negative Components Balance (which is the SUM of each row Value 1 - Value 2 only when Value 1 - Value 2 < 0) at my reporting date which is again 2022-12-31. Result: Both above calculations seem straightforward in this case, but there is an additional constraint we should consider: Each operation (Value 1 - Value 2) can only be performed if Date_1_SID AND Date_2_SID are both before or on the same day of the reporting date . This condition is met for all the dates in the above dataset if my reporting date is >= 2022-12-15. But what if I need to make the above calculation available for any date in the Gregorian calendar? Or, in other words, if my reporting date is "any" date? It's a bit more triky... For semplicity let's consider we need to present the "Overal Balance" for each end of month of year 2022, and let's assume our "reference date" for "reporting date" is Date_1_SID which is always populated in the above dataset. This is the result: We used the Excel function SUMIFS() in this way: Overall Balance @ Reporting date = SUMIFS( Value_1_Range, Date_1_SID_Range, "<=" & Reporting_Date_SID ) - SUMIFS( Value_2_Range, Date_1_SID_Range, "<=" & Reporting_Date_SID, Date_2_SID_Range, "<=" & Reporting_Date_SID ) And now my question: How to calculate in Excel the "Total Negative Components Balance" for the same reporting dates? If I'm not mistaken, this should be the expected result: PS: I wanted to solve this in Excel first, to learn the lesson, but then I need to implement the above logic in DAX since Excel cannot manage the size of my dataset made of millions of rows. So a DAX answer would work too.28Views0likes1CommentIf you thought Spills killed CSE, read this post
I build financial models. All financial models I have ever seen do the same thing - they show one reporting date per column. Every next reporting period evolves from the previous financial close. That is, the calculation order in such models is strictly A1 to An, B1 to Bn, ... N1 to Nn. There may at times arise the need to perform complex calculations that produce more than one result. Today, we can (program those in Lambdas and) spill the outputs across multiple rows (within the same column). Alas, Excel's performance resizing Spills is absolutely atrocious. Deploy such a formula across the columns of your model and you can literally watch the pixels set. One column at a time. Things settle after the initial run. There is then no need to resize ever again. Just so long as the calculation works out alright. Unfortunately, stuff happenz. And when that calculation errors out, you are back to watching pixels set as Excel replaces your multiple result rows with one row of #VALUE!. Once you fix that error, well, then you are back in for another treat of umpteen resize passes... Such a spreadsheet is frankly unusable. Now, I noticed with some astonishment that there is a simple fix for this nonsense: Just enter your spill formula with Control-Shift-Enter to morph it into an array formula. Sure, we all hate those. But all the hassle around editing is a price worth paying for Excel suddenly attaining Warp speed... Post Scriptum: There is another "solution" to this problem. Instead of performing the calculation once to spill 5 results, perform the same calculation 5 times over and return 1 result each time. If you think this is awful, you are of course correct. But ... to my amazement, doing this easily beats the Spill (no surprise - even using an Abacus probably will) and there was no telling a difference to CSE. I was baffled. Then I realized what Excel got up to. Excel understands that these 5 calculations are independent of one another and will happily parallelize them across multiple CPU cores. So, while this insane solution hammers the CPU, the nature of a financial model is such that it mostly cannot exercise more than one core anyway (it cannot be multi-threaded), such that on the occasion that we are being stupid, there are plenty of cores idling around that can bail us out... :) Post Post Scriptum: If for some reason you do not wish to use CSE, make absolutely sure that when stuff happenz your Spill returns 5 bad results. That avoids Excel having to resize that range when an error occurs.ecovonreinFeb 08, 2025Iron Contributor13Views0likes0CommentsDegenerate MAP blows up Excel
Does this formula work for you? =MAP(0,1,LAMBDA(x,y,x+y)) Which Build are you on? I am stuck on 2406 because all Builds since blow up when running my spreadsheets - which, as I type this, JUST MIGHT be down to this nonsense. Not only does this innocent instruction return #VALUE!, it also appears to corrupt Excel beyond recovery. Which is a problem because the MAP(LAMBDA( phrase is a wrap one needs to deploy when a Lambda(a,b... might have to handle vectors for a and b to return a vector of results for a computation that will not support such vector inputs natively. The cure for this nonsense on my Build appears to be =MAP(HSTACK(0),HSTACK(1),LAMBDA(x,y,x+y)) Unfortunately, it is hard to remember this. I might have to scour my Lambdas. The problem only appears to concern MAPs with multiple vectors. =MAP(1,LAMBDA(x,x)) is just fine.ecovonreinFeb 08, 2025Iron Contributor14Views0likes0Commentshow to add a confidential watermark in excel when printing
how to add a confidential watermark in excel when printingcommunityyaky19Feb 08, 2025Copper Contributor11Views0likes1CommentCan you search for multiple things at the same time
Each day I receive a list of about 50 unique 6 digit numbers on a spreadsheet. I then have to check each of these numbers individually on a second separate spreadsheet to avoid duplication of work. To do this I copy one unique ID from the 1st spreadsheet and use the 'Find' function on the second spreadsheet. Doing this 50 times is quite time consuming so I was wondering if there was a way of searching all 50 at the same time. I am in no way tech/excel savvy so please any replies can they be in 'idiots guide'. Thanks everyone!SAndrewFeb 08, 2025Occasional Reader12Views0likes1CommentCalculate which parts make up the total sum
I Have a problem with receiving only a total sum and then I need to figure out what partial sums in a long list that makes up that total. Is this somtehing that is possible for Excel to calculate, or in some other application. Doing it manually takes a long time and is sometimes impossible.MachinehandlerFeb 08, 2025Occasional Reader19Views0likes1Comment
Resources
Tags
- excel42,163 Topics
- Formulas and Functions24,441 Topics
- Macros and VBA6,347 Topics
- office 3655,930 Topics
- Excel on Mac2,615 Topics
- BI & Data Analysis2,327 Topics
- Excel for web1,876 Topics
- Formulas & Functions1,716 Topics
- Need Help1,703 Topics
- Charting1,606 Topics