User Profile
Riny_van_Eekelen
Platinum Contributor
Joined Sep 04, 2019
User Widgets
Recent Discussions
Re: Sorting data on multiple sheets
Donsclan So may I assume that you are using a regular SUM function that points to cells for specific individuals in the weekly sheet? If so, look into using SUMIF or SUMIFS instead. These functions allow you to sum values in an entire range based on criteria in another range or other ranges. But perhaps better to get rid of the weekly sheets all together and create one table with whatever you are tracking, in a tabular lay-out. Much easier to analyse/summarise than data in separate sheets.Re: Excel Tables and Conditional Formatting
BaltaD Worked on something like this a long time ago and can't remember if I made this up myself or found it somewhere on-line. Anyway, I now found it in my saved example files and share it here. The CF formula I would use to color the rows based in the team number in column B is this: =MOD(SUM(IF(FREQUENCY($B$2:$B2,$B$2:$B2),1)),2) Can't upload the file. The system is a bit screwed lately. Therefore just a picture.Re: row count in status bar
As you have noticed, the status bar displays the statistics of all cells selected. Besides Count and Average you can show Sum, Numerical Count, Min and Max. Right-click on the status bar and you see all the elements that can be displayed in it. An analysis by column and counting columns in the selection are not available.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.
