User Profile
m_tarler
Bronze Contributor
Joined 2 years ago
User Widgets
Recent Discussions
Re: [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.Re: Wrap Text Not Wrapping Correctly
And to add baffle to confusion you can try zoom in and zoom out because I have found that to cause word wrapping to change also. When I'm reviewing documents where word wrapping may matter I have tried using 160% or 140% zoom as that seemed to be more 'true' to the print to pdf word wrap but that is probably dependant on the font and who knows what else.Re: Compund XLOOKUP returns #VALUE error
the ranges aren't the same size for the lookup and the return ranges: =XLOOKUP($A8,'[Sales Data by Month.xlsx]Sheet1'!$A:$A, XLOOKUP(I$7,'[Sales Data by Month.xlsx]Sheet1'!$1:$1,'[Sales Data by Month.xlsx]Sheet1'!$R$2:$AF$64)) so in the second XLOOKUP the lookup range is an ENTIRE row but the return range is only columns R:AF and even if that worked it and it returned one of those columns it would only return rows 2:64 and the first XLOOKUP is looking up on a range of an ENTIRE column. using INDEX and XMATCH would probably work: =INDEX('[Sales Data by Month.xlsx]Sheet1'!$R$2:$AF$64, XMATCH( $A8,'[Sales Data by Month.xlsx]Sheet1'!$A:$A), XMATCH( I$7,'[Sales Data by Month.xlsx]Sheet1'!$1:$1) ) but if XMATCH doesn't find a value or finds a match outside the bounds of the R2:AF64 range counts that would be a problem and since you are starting at R2, would that XMATCH values be correct? e.g. A8 in A:A will be 8th row but index of 8 in that range would be the 9th row on the sheet.Re: Using data
and with the newer TRIMRANGE you can make that sheet1 range arbitrary large with the TRIMRANGE function or just use the operator/shortcut (.): =XLOOKUP(C2:.C99999, Sheet2!$A$2:$A$10000, Sheet2!$B$2:$B$10000, "") Alternatively I higly recommend using TABLES (Home -> Format as Table). Then if you label the Table on Sheet 1 as "DATA" and the Table on Sheet 2 as "PODS" (you can name the table by after clicking and setting the data range a Table you then click on the 'Table' menu and edit the Table Name field). The you have: =XLOOKUP([@Comment], PODS[Comment], PODS[Pods], "") and then it automatically expands (and contracts) with the amount of data in the corresponding Tables AND it is READABLE so you know you are looking up the 'Comment' on this line in the Table called PODS for the corresponding 'Comment' and return the corresponding 'Pods' value. You don't have to hunt for columns C on this sheet and A and B on some other sheet.
