User Profile
m_tarler
Bronze Contributor
Joined 2 years ago
User Widgets
Recent Discussions
Re: Remove sub-folder urls if the parent folder url exists
I like Harun24HR approach using REGEX but that will only remove 1 layer of subfolders and only subfolders that are numbers. Here is a more general solution but I resorted to SCAN function: =LET(in,$A$2:.$A$99,I,SORT(in),UNIQUE(SCAN(TAKE(I,1),DROP(I,1),LAMBDA(p,q,IF(ISNUMBER(SEARCH(p,q)),p,q)))))Re: Average values for several parameters based on date and time ranges
So the easiest is to use the function AVERAGEIFS. Here are 2 examples: a) using ranges: =AVERAGEIFS($G$3:$G$18,$F$3:$F$18,">="&B$3+B$4,$F$3:$F$18,"<="&B$3+B$5) b) using table reference (need to format datatable using Format as Table) =AVERAGEIFS(Table1[Parameter 2],Table1[Date - Time],">="&B$3+B$4,Table1[Date - Time],"<="&B$3+B$5) as you copy down/right you will have to update some of the references accordinglyRe: Running percentage
It is really hard to help with limited information. A sample workbook is really helpful and if you can't attach it then providing a link to a cloud storage like onedrive or google drive works too. That said, I have no idea on how you have the data laid out. For example is this a running list of tasks and dates completed? or is this just a list of task completed this month? Let's pretend you have column A as task and column B as date completed. And then you want to count all tasks with completed dates this month and divide by #days this month: =COUNTIFS(B1:.B999, ">"& EOMONTH(TODAY(),-1) ) / (TODAY() - EOMONTH(TODAY(),-1) ) But your specific case may be very different depending on what data you have and how your data is arranged. At least I hope this might get you started.Re: I need to learn how to use the LET function in Excel...
well, I might disagree because depending on what they are checking a LET might help. For example if they need to use a LOOKUP to get a value that then they want to use multiple times in one or multiple conditionals then the LET could be very helpful. Either way, learning LET and IFS and other function is ALWAYS a good idea. I recommend you just do a search for 'tutorial on LET in excel' and you will get lots of options. It depends if you prefer a video like: https://www.youtube.com/watch?v=PJtHWgq6fTg or a more text based like: https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999 good luckRe: [Excel Formula?] How to calculate a product's sales rank based on 3 criteria
so i'm not sure what is expected really. I see a few likely options: a) regardless of the year for this row give what rank it would be if it was in that year b) only return the actual rank that row placed in that year c) create a new chart using only the Product name and show the 2021,2022,2023 results for a) you could use: =XMATCH($C2,SORT($C$2:$C$9*($A$2:$A$9=2021),,-1),-1) for b) you could add the conditional/if around it: =IF($A2=2021,XMATCH($C2,SORT($C$2:$C$9*($A$2:$A$9=2021),,-1),-1),"") for c) a pivotchart can give you the % rankings for each year which is close: but instead you could use PIVOTBY and get it: =PIVOTBY(B2:B9,A2:A9,C2:C9,LAMBDA(a,b,XMATCH(SUM(a),SORT(b,,-1),-1)),0,0,,0) and attached is the file with the different examples EDIT: and here is another option if you want that row to show the result of that product in that year so for example row 1 has 2022 Red Hat and you want the 2021 column to show how Red Hat did in 2021 even though that is found in a different row: =XMATCH($B2:$B9,TAKE(SORT(FILTER($B$2:$C$9,$A$2:$A$9=--RIGHT(J$1,4)),2,-1),,1),-1) file updated with this option tooRe: How to resolve formula discrepancy Excel/SharePoint
As already mentioned by Kidd_Ip, the problem may (or is likely) the syntax for the online uses semi-colons instead of commas. That said, can I comment on the formula itself: =IF( ISERROR(INDEX(Form1!F:F, ROW()-90)), "", IF( INDEX(Form1!F:F, ROW()-90) = "", "", INDEX(Form1!F:F, ROW()-90) ) ) so the 1st part checks if the INDEX is an error and print "" if so 2nd part checks if INDEX returns "" and print "" if so 3rd part returns the answer from INDEX so why even do the 2nd part because if it will return "" anyway then just skip to part 3 as for the ISERROR why not just use IFERROR so it could look like this: =IFERROR( INDEX(Form1!F:F, ROW()-90), "") and that said, I'm not a fan of using hardcoded values and that 90 could be a problem. For example you or someone else a year or 2 from now decide the first 10 rows aren't needed and you just Delete those rows. Then all the data shift up by 10 rows and now instead of 90 it should be 80. So nothing is perfect but I would suggest an option might be to use the cell $F$90 because when those rows are deleted that cell $F$90 will automatically be adjusted to $F$80 so something like this: =IFERROR( INDEX(Form1!F:F, ROW()-ROW($F$90) ), "") note the column F doesn't matter but the '$' does so if you copy or drag down that cell reference doesn't change.Re: Conditional Formatting possibly
The easiest thing to do is insert a column to the left of column A. Highlight the range (should now be B2:O4) then in conditional formatting you want a new rule and based on formula. The formula will be something like: =(B$3<>"")*ISEVEN(COLUMN(B$3))+(A$2<>"")*ISEVEN(COLUMN(A$2)) which basically is always checking row 3 and either the cell in the same column (e.g. B3) if that column is even or the cell is the column before (e.g. A3) if this column is odd. as you can see adding that extra column to the left and pushing everything over 1 column makes it easier to check both this column and the column before (which wouldn't be possible if the range started in column A)Re: Concat and Concatenate problem
not sure what the question or problem is. the second formula should work for the first row and then fill down. CONCATENATE or TEXTJOIN functions would also work. that said, for this simple case you can use: =C2:C39 & " " & D2:D39 but if it is more complicated and a lot of columns your could =BYROW(C2:D39, LAMBDA(r, TEXTJOIN(" ",,r)))Re: Will I save resource when adding an If function before complicated functions?
Sorry let me clarify. If the conditional is an array. So if we tweak your example: =IF(A1:A2=TRUE, AVERAGE(SORT(UNIQUE(SEQUENCE(100000,500)))),0) Even if BOTH A1&A2 are False it will still take a second or so to calculateRe: Find and export from one spreadsheet to a new one
try something like: =FILTER(Sheet1!1:.1048576, ISNUMBER(SEARCH("Safety Team", Sheet1!$AA$1:.$AA$1048576)),"none") note that the last row in column AA must match the last used cell on the sheet for this to work. Alternatively replace the 1:.1048576 with the actual range of the data being used or better yet Foramt as Table and reference the TABLE accordingly.Re: Protect Sheet / Workbook doesn't work for me?
those sheet and workbook protections are only for protecting from modification. If you want to lock the whole workbook from open (or modification) that is done in the SAVE menu. If you hit Save As and then you might need to click on the 'more options' to get the classical window (or just click F12). There is a 'Tools' button / drop down next to the save. In that you select General Options and then select the type of protection and the password: Please note this protection is stronger than either the sheet or workbook protection (i.e. harder to crack) but I would still NOT consider this proper security for any truely sensitive information. Treat all protections like those little luggage locks you put on the zippers, something to keep the casual nosey person out but will not stop someone who really wants to get in.
