formulas and functions
25455 TopicsSupport for lists-in-a-cell and functions such as FLATTEN disappeared
A strange thing happened to me with Excel. I am signed up for Current Channel (Preview), with Microsoft® Excel® for Microsoft 365 MSO (Version 2611 Build 16.0.20625.20076) 64-bit. When Microsoft released the new list-in-a-cell update and the new HASANY, FLATTEN, etc. functions, they were made available in my installation and I used them in a couple of workbooks. But a week or so later, there was an update to Excel (or the whole Office 365 suite) and those functions just disappeared. If I try to open a workbook which uses these features, I get the message "This workbook is set to Compatibility Version 3, which is not supported by this version of Excel." It offers to "Downgrade" the workbook, but that only results in a workbook full of error messages. It happened to me before that a roll up of a new function was delayed for a while, but I've never experienced a roll back. Was it just me or part of a plan? I asked CoPilot, but it has no idea, strangely enough.139Views0likes4CommentsSort in alphabetical order; sub total groups
Sort in alphabetical order : button is active but not responding to command, also i'm trying to sub total groups of number and button stays grey unavailable. Microsoft support can't do anything. May I have help somewhere. We tried update, reparing, uninstal and instal with no results11Views0likes0CommentsNumber of days + or -
I am trying to get the difference in the number of days between two dates and it works if the target completion date is before the completion date using this formula: =DATEDIF(H3,J3,"d"). I do get the correct number of days using this formula, however, I would ideally like is a plus or minus in front of that number (e.g., + if the project went past the target date, and - if the project was completed sooner). I can apply conditional formatting to the rows after to change the color of the number if I get that far. When the completion date is before the target completion date (e.g., completed project early), I get the #NUM! error, which I expected. But I cannot find a way to get exactly what I am looking for. Hopefully I am explaining this clearly. The formula in L4: =DATEDIF(H4,J4,"d")806Views0likes4CommentsComplex numbers in Excel
Did you know that Excel can handle complex mathematical operations with complex numbers? Complex numbers, expressed as "a + bi," where 'a' and 'b' are real numbers and 'i' is the imaginary unit, can be powerful tools in various scenarios. Think engineering, physics, signal processing, or even financial modelling. Excel offers native support for complex numbers, and you can perform basic operations like addition, subtraction, multiplication, and division just as easily as with real numbers. If you haven't explored this yet, give it a shot! It's as simple as entering a formula like =IMSUM(A1:B1) or =IMPRODUCT(A1:B1) One nifty feature is the ability to convert complex numbers from rectangular to polar form and vice versa. This can be a game-changer in certain calculations, especially when dealing with phase angles or magnitude-based analyses. For this you can make use of the =IMABS(A1) and =IMARGUMENT() functions. As complex numbers are considered to be text within Excel you will need special functions to perform arithmetic with them, such as IMSUM, IMSUB, IMPRODUCT, and IMDIV. Excel can help you plot complex numbers on the complex plane using the XY Scatter chart! you will need the IMREAL and IMAGINARY functions to extract the real and imaginary coefficients from the complex number to use them to plot the numbers. This feature can be invaluable when dealing with complex data sets or analysing the behaviour of complex functions. If you're interested in learning more about complex numbers, I made a video covering all functions that work with complex numbers in Excel: https://www.youtube.com/watch?v=_A2DIUibkmk Have you worked with complex numbers before in Excel? What specific use case did you have? Were there any obstacles you had to overcome?25KViews2likes4CommentsHappy holidays: a tree drawn in Excel cells with one formula
Happy holidays, everyone. Here's a small seasonal workbook for anyone who likes conditional formatting: a tree drawn in cells that lights up row by row. How it works: every tree cell has the same formula. It returns 1 (lights off), 2 (green), or 3 and 4 (red and gold lights). Home > Conditional Formatting has one "Cell Value equal to" rule per number with a fill colour, and the cells use the custom number format ;;; so the digits stay hidden. I2 is the switch (Y or N) and I3 is a step from 0 to 24. Each step lights one more row from the top, the trunk comes on at 13 and the star at 14, and every step swaps the red and gold lights, so stepping through it looks like blinking. The formula in every tree cell: =IF(AND($I$2="Y",$I$3>=ROW()-4),IF(MOD((ROW()-4)*7+COLUMN()*3,5)=0,IF(MOD($I$3+ROW()+COLUMN(),2)=0,3,4),2),1) ROW()-4 is the tree row, so a row lights once the step reaches it. MOD((ROW()-4)*7+COLUMN()*3,5)=0 picks about one cell in five as a light, and MOD($I$3+ROW()+COLUMN(),2) flips each light between red and gold on every step. The workbook is attached, with no real data in it. I checked it in Google Sheets and another spreadsheet app rather than Excel itself, so let me know if anything looks off in your version. (Drafted with AI assistance and checked before posting.)135Views0likes0CommentsHow to change the columns a formula will get its data from
Here is a dummy example of what my spreadsheet looks like, the end date/time can be in different columns based on external factors. a b c d 1 Start date/time Other date/time End date/time Amount of time 2 January 1 2026 12:41:55 PM January 1 2026 12:50:00 PM 3 January 1 2026 3:30:50 PM January 2 2026 5:00:00 PM 4 January 2 2026 4:00:55 PM January 2 2026 10:00:00 PM 5 January 13 2026 12:30:00 PM January 13 2026 12:33:00 PM How do I calculate the difference in time in column D based when the end date and time may be in one of two columns? Before I would just subtract one column from another and then format the cells to create a readable output automatically, but I need a formula to take into account two columns and to choose the one that is not empty. I am unsure how to write the in then else in a way that Excel can understand. What I have tried: = IF c2 = " " THEN b2-a2 ELSE c2-a2181Views0likes7CommentsCombining IFS formulas
Hi, I'm trying to make a formula to say "yes" if certain conditions are right for two columns but I'm unsure how to make the formula. I usually use IF, but the G column cell is acceptable if the result is >=0.5 OR 1+,2+,3+, or 4+ based on if it went on an instrument or was manually read. The H column cell is acceptable if the result is <0.080 or "clear". I need the I column cell to say yes if both the G and H column cells are acceptable. I also don't want anything to show up in the I column cell if the other two cells are blank. I tried using IFS but couldn't get it to work right because I had separate IFS for each column and couldn't figure out how to combine them. I've attached an example of the worksheet I'm using. Ignore that the cells say 24, I was playing further down in the worksheet. Any help is appreciated. Thanks!Solved155Views0likes3CommentsSlicer to create Dynamic Chart Titles
Hi. I have a chart that the title is the name of a category. There are 6 categories, let's say: A,B,C,D,E,F I created a Category slicer, so that when I select whatever category, the chart title updates with that Category label. I know how to create a formula that shows the title as whatever categories are selected, e.g. if I select A,B,C,D, it shows "A+B+C+D", and if I select all 6, then it shows "All Categories". But I want the formula to be such that if 4 out of 6 categories are selected, it would show "All Categories excluding..." where the "..." are the categories which are excluded, so instead of "A+B+C+D", it would show "All Categories excluding E+F". That's where I am stuck. I want it so if the majority of categories (i.e. 4-6) are selected, it would display the "All Categories excluding...", but otherwise it would display the categories separated with the "+". Hope my question makes sense. Can you advise me how to proceed. Thanks!179Views0likes2Comments