User Profile
Riny_van_Eekelen
Platinum Contributor
Joined Sep 04, 2019
User Widgets
Recent Discussions
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 from dynamic array challenge
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 from dynamic array challenge
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.Re: PowerPivot not returning correct data
Kindly ignore Kidd_Ip 's AI generated answer. Just use the Month and Invoice number fields from the 1-side of the relationship and then it will work as you expect. https://onecom5409298.sharepoint.com/:x:/g/EUjiPZibwUBBgmIRbXMUIRABXdytx63J1VbZ1WTQhvEFSw?e=6uc8QbRe: Vlook up issue related to format of cells between two different files
I suspect that your 'tiger codes' in file B are texts whereas the 'name codes' in file A are numbers. Simply formatting both columns as Number does not change a text to a number or vice versa. VLOOKUP will thus not find a match and always return #N/A. Probably easiest to transform column B in file A to text with Text-To-Columns. You find the icon on the Data ribbon. Save you file before doing this so that you can always revert back it in case it doesn't work as expected! Select B2:B616, Text to columns, step1 change nothing, next, step 2 change nothing, next, step 3 set the column data format to Text, Finish. Now the name codes in file A are all texts and VLOOKUP should be able to find matching codes from file B.
