User Profile
Riny_van_Eekelen
Platinum Contributor
Joined Sep 04, 2019
User Widgets
Recent Discussions
Re: Excel auto-truncating timestamps sub milliseconds
jsok This is a user forum where you can ask questions and get help with Excel. We do not collect suggestions for product improvement. Please submit your feedback via https://feedbackportal.microsoft.com/feedback/ or use the Feedback button from the Help ribbon.Re: Unircadenas => Delimitador
Paco Castro Pérez Not sure if the I understood the Spanish to English translation completely, but I believe you want to know the code for a "line feed" to be used in TEXTJOIN. Try CHAR(10). In Spanish Excel that would be CARACTER(10) The formula would then look something like this: =UNIRCADENAS(CARACTER(10); VERDADERO; A1:B20)Re: Quotation Marks - Losing my mind over Excel error
Riccardo31 (=C4," ",B4) is not recognised as a proper formula. Perhaps you want to try something like this: =IF(C4 , "" , B4) In Italian that would be: =SE(C4 ; "" ; B4) If this makes no sense, please show us the context in which you want to enter a formula.Re: Return a value based on different parameters
I would recommend you to avoid nested IF functions with hard-coded values, especially when you need many of them. Better to use a structured table with Option Chosen and Return Value as a lookup table for the XLOOKUP function. Call the table "options", for example. Then the following will work just fine. =XLOOKUP(F3,options[Option Chosen],options[Return Value]) And then you can point the drop down list to the first column of the options table. Add or delete options later and the drop-down will automatically update. Other ways (just a few examples) that avoid nested IF functions (though still with hard-coded values) could be: =SUM(--(F3={"Not Started","Started","In Progress","Nearly There","Complete"})*{0,0.25,0.5,0.75,1}) or =SWITCH(F3, "Not Started",0,"Started",0.25,"In Progress",0.5,"Nearly There",0.75,"Complete",1)Re: Chart updates inconsistently when dynamic array resizes
Lorenzo I believe the problem lies in the fact that you don't include the years column in the dynamic array. With TOPN 5 it just gives you 5 series. When you then add the year numbers (non-dynamic) as the x-axis labels it breaks. What I did is write a formula in UI, B6 that references TOPN_Pivoted and adds a proper date column to it. I'm sure you can think of a more elegant way to achieve this. =HSTACK(VSTACK("Year",BYROW(TAKE(TOPN_Pivoted,-6,1),LAMBDA(y,DATE(y,1,1)))),DROP(TOPN_Pivoted,,1)) Create a chart from that array and it will come out like this: You can see that the Year column with proper dates is automatically picked up as a row header. If you don't transform the year numbers into proper date values you'll get this: It includes "Year" as just another number series. All series are stacked on top of each other and only the last one is visible. Switch Rows/Columns and you'll get this: The blue line at the top is the series "Year" with values 2020 to 2025, being far out of touch with the other series which all lay in the bottom of the chart. Not sure if this helps. Kindly ignore if it doesn't.Re: Chart updates inconsistently when dynamic array resizes
Lorenzo I keep coming back to this post over and over again and also saw your cross-post on Q&A with no answers. I guess I just didn't understand what you were trying to demonstrate. Now I downloaded your "Workaround" file, and the first thing I note when I change the TOPN number in the UI tab is that nothing happens. Went to the DATA tab and saw that the pivot table was not set to "Auto Refresh". Most likely because your Excel doesn't support it. Switched on Auto Refresh, changed the TOPN number again in the UI tab. Then this happens: It seems the UI tab is protected. Removed the protection and everything refreshes automatically upon changing the TOPN number in the UI tab.Re: Formula to compare a number as text and a partial match?
WL1 Your formula returns FALSE in C2 since the number 16710057 is not found within the string "016810057". It's not the missing zero thought the 7 in the third digit that results in FALSE. No need to use IF( ..........., TRUE, FALSE) by the way. =ISNUMBER(FIND(B2,A2)) will work fine.Re: Saved CSV opens with dates even when saved as text
Start by disabling all the "Automatic Data Conversion" options and try again! As a general comment, it's not wise to not open a CSV file directly in Excel as Excel will try to be smart and guess what your textual data is about, often leading to undesired results. Best to import a CSV via Power Query (Data ribbon, Get & Transform Data, From Text/CSV) ...... so that you have full control over the how the texts are transformed into numbers, dates or just stay plain texts. Alternatively, you can use the text-to-columns feature, also from the Data ribbon in the 'Data Tools' group, though it's not as sophisticated as Powe Query.Re: Formula help - why doesn't this work for January dates please?
Please clarify what this odd formula is trying to achieve. Both MONTH functions return an array of TRUE or FALSE if the dates in the ranges are in December (true) or not (false). Then adding them together results in an array of 0's, 1's or 2's. For instance, if the fist date in E falls in December but the first one in H does not it returns 1 + 0 = 1 for that instance. No need for the double "--" and what is SUMPRODYCT supposed to do? Don't you just want to SUM the resulting array? And what 'random total' do you get for January? Share your file or at least add a screenshot so that we can see what you are working on.Re: How can I display negative values for time in calculation results
As mathetes mentioned, there's no such thing as negative time. BUT..... switching Excel to use the 1904 date system will allow you to calculate with and display negative time values. Though, changing the data system will shift all dates in your file by 4 years. If you don't have real dates in this particular workbooks that may not be a problem then.
