User Profile
m_tarler
Silver Contributor
Joined Nov 28, 2023
User Widgets
Recent Discussions
Re: Help with summing totals using multiple texts
Hi Sazzy, Thank you for sharing the workbook. on the MTA ('26) tab I created a table with the logo sizes. I found a number of issues in the process. First off the table you had didn't have the right logo size options for MTA. Next i found in the tables on the 'logo sizes' tab you had entries like: YOUTH XS (3/4yr) Hoodie & Zoodie which means the "YOUTH XS (3/4yr) Zoodie" would not match that entry (as I mentioned the text for all entries need to be identical). I also found 2 other differences including Tee vs T-shirt and Strap Top vs Sprag Strap Top. I made corrections on the MTA table but didn't touch the S.S table. So the updated formula I use actually builds the logo sizes table with all the possible sizes based on the logo sizes tab and then does the counting. This is great but I found 1 problem. The number of orders was 20 but I found 21 logo sizes for the smaller sizes (i.e. not counting the larger back logos). I believe this is because "L Spag strap top" will match with "XL Spag strap top" and both of those match with "XXL Spag strap top" and so on. This is a problem with the configuration you have. Even if you have a more consistent comma deliminated list or always had each item on its own line and added an extra line on each cell we make the search explicitely look for a line feed immediately before the item text. but IMHO these are just 'hacks' to make it work. (btw I put everything on its own line and added the extra line return and added the preceeding line return to the search to make it work) As for what I feel it a better way to set it all up: I attached a file in a prior reply with a suggested different layout. It has the main data table and then a item table that can include the logo sizes there and finally a table to sum up the logo sizes. By adding the logo size lookup to the data table the sum becomes really easy to find. That item table showing all the different items and sizes and logo sizes could be on a separate tab and have multiple columns for different customers and front and back. then the lookups would just pull from the corresponding columns.Re: How XLOOKUP arguments apply in this case
so you are correct this is backwards from the typical usage. However here is one option: =XLOOKUP(J1,BYROW(B4:AF7,MAX),A4:A7)&" "&XLOOKUP(J1,BYCOL(B4:AF7,MAX),B3:AF3) or by naming the ranges it looks like this: =XLOOKUP(maxCount,BYROW(data,MAX),months)&" "&XLOOKUP(maxCount,BYCOL(data,MAX),days) either way I used BYROW(...MAX) and BYCOL(...MAX) to find the max value in each row/column so I could then use the xlookup accordingly (i.e. 1 row to output from a row or 1 column to output from a column) there is a problem with this solution in that if you have a tie it will take the first column (day) it finds and the first row (month) it finds which may not be one of the real max day results (e.g. if April 1 also has 758 then the output will be Jan 1 instead of Jan 3). So a solution that will correctly output all the max days could be: =TEXTJOIN(", ",1,MAKEARRAY(12,31,LAMBDA(m,d,IF(INDEX(data,m,d)=maxCount,INDEX(months,m)&" "&d,"")))) so in this case I make an array that is 12 rows by 31 days (size of the data) and look at the corresponding day and if it matches the maxCount then return the Month Day and then join all those values. alternatively you can also do it this way: =TEXTJOIN(", ",1,TRIM(IF(data=maxCount,months,"")&" "&IF(data=maxCount,days,""))) in this case I just act on the whole data table to apply the month and then again to apply the days and then textjoin againRe: Filtering an Array based on a list of Criteria
You just need to ADD the conditions together: =FILTER([projectNames], ([name]=[PM])+([name]=[PE]) + ([name]=[SUP]) ) I notice you said you used ISNUMBER(MATCH)) but not sure why or how you have that setup. I could see ISNUMBER(SEARCH(name, PM)) if that field could contain multiple names (e.g. a list of names) and the name you are searching for may be only 1 in the field. hope that helps you figure it out but if not maybe more info and a copy of the sheet would helRe: How to keep just the filtered data when saving
so there are a number of things here. the main thing is that it is a .csv file so it will NOT save any formatting, filtering, etc it will only save the data itself. This could be good as you won't have 'hidden' data that a user could just unhide. As for how to get this done, I have to next ask what you mean when you say "after running my filter" and what exactly that means. is that a macro/VBA/script? is that using the quick filters in excel? is that using advanced filters in excel? is that hiding rows/columns and not using data filters? I ask because 'hiding' rows/columns won't work but 'filtering' rows will work with the method below: So if you use an excel filter (like the quick filters or advanced filter or a macro that applies these methods) you should be able to copy the resulting 'table' and then paste it in a new location then delete the orginal data range. So for example original data is in rows 1:100 and you filter the data and then copy that range and paste it starting in row 110. Then highlight rows 1:109 and right click and hit delete rows. OR paste the data in a new sheet tab and delete the original tab. Then save the .csv and you will have permenantly removed that unwanted data. If you are manually hiding rows or columns, choose to Delete those rows or columns instead. If it is a macro or VBA then have that code delete instead of hide. Hope that helpsRe: Using calculated columns in pivot table
maybe I'm missing something but isn't that just Average of working time? So on the 'Field List' add Working Time to the Value section a second time and go to 'Value Field Settings' and change calculation to Average (then you can also change the Name also).Re: Shop Inventory
Again, having more information like the actual sheet layout and formulas and desired output would help. You say "I dont have these held in table" but where are they? What does "result is dependant on the MAXIFS" mean? I image you have some sort of list of dates and statuses as you say "Have list of Dates". My example uses reference to a range of data that is formatted as a table but there is no reason you can't substitute the corresponding range(s). so for example if you have: A B C 1 Date Status 2 1/1 Pending 3 1/2 No Order 4 1/3 paid so "Table1" in this case would be A2:B4 and "Table1[Date]" would be A2:A4 and "Table[Status]" would be B2:B4 but using the names is much easier to read and you can substitute your particular ranges accordingly.Re: Shop Inventory
having a sheet would help but let's say the data is in Table1 and the date is in a column Date and the other column of interest is Status so Table1[Date] and Table1[Status] then =FILTER( Table1, (Table1[Date]<TODAY()-7)*(Table1[Status]="No Order") + (Table1[Date]>TODAY()-7)*(Table1[Status]="Pending") + (Table1[Date]=TODAY())*((Table1[Status]="paid")+(Table1[Status]="payment due") ) , "none found") so basically in the conditional part of the Filter I 'add' each condition to act as an 'OR' and 'multiply' each condition to act as an 'AND'Re: Excel says two numbers are not equal
There are much better detailed descriptions of this but basically excel needs to use binary to represent those numbers. Binary can represent whole numbers very easily but even then very large numbers reach a limit. For fractions it is even more so. So simply increase the number of digits being displayed and you will see: so excel has to represent 6827.81 using binary and then -6613.99 using binary and add those 2 numbers together and those 2 representations happen to have just enough round off error to creep into a very low digit that is within the significance kept by excel. If you break those numbers up to be 6827 + 0.81 it doesn't happen or -6613 - 0.99 it doesn't happen. There are many of these rare occasions where that last bit rounds (or flips) up. If you are really interested in the details I welcome you to dive deeper into Excel round off errors but just make sure to use ROUND(..) when checking for exact values or >= or <= when comparing values.Re: Microsoft Excel Update Changed Formula
I don't know why it is showing you that now. My version still recognizes MAXIFS and excel is overly conservative about backwards compatibility (many outdated functions are still supported when possible). I would double check what version of excel you are running. Although strange it might be you had a very old version installed and for some reason your shortcut is running that old version now or something? You can check under File->Account and then on the right side by 'About Excel'. Also have you tried the online version? have you tried typing in a cell =MAX and see what functions show up in the tooltip popup and if MAXIFS is there? I am much more interested in figuring out why this is happening but here is an alternative approach I think would work and you might want to consider regardless: =IF(ISBLANK($A6),$A2,MAX($A2, A$7:A&41*(D$7:D$41>0) ) )Re: How to keep the result of the formula from appearing down the column before a number is added
I think what you want is a formula like this: =IF( N(B10)+ N(D10), SUM(B10,C9),"" ) So basically if either B10 (Home score) or D10 (Away score) is a number then report the accumulating sum. If not, then put in a blank (""). For the Away it is nearly the same but summing those cells: =IF( N(B10)+ N(D10), SUM(D10,E9),"" ) Here is the result: I have added this option in SnowMan55's file (if it will attach for we'll see....)Re: Help with summing totals using multiple texts
ok so this is NOT the best way to have the worksheet set up. For example what will happen if someone order 2x (or more) of something? or even if they want 2 different sizes of the same item. next I don't understand the 'blue' section and how that defines the logo size. I see a number 6 on both rows and the second row has the number 18. lastly i caution that those entries will all have to be exactly the same for the item & size in all lines (but this is true in most cases but in my proposed format it is a bit more 'simple') I highly recommend you use data validation if you aren't already. so for you your set up, a formula that should work to sum up the number of cases you find any of the text in that list of items from the Table range (note I defined the data found on your image in A6:I18 as "Table1"): =SUM(COUNTIF(C34,"*"&Table1&"*"))-COUNTIF(Table1,"") so although that may work for you I suggest re-doing the sheet something more like this: so on the left is a data entry table where you have each line be 1 item (item type + size) with a quantity in columns J:L is a table for all the items and sizes and corresponding Logo Sizes I added column G to the data entry table for the logo size, but that is a lookup formula so you do not enter that cell, it is automatically calculated based on the table Finally in O:P is a simple table to sum up the orders based on logo sizes There are MANY ways to do this, and this is just one but I highly recommend you reconsider your original format. I will try to attach this sample file with both your tables and my suggestion.Re: Can we get more guidance and feedback on the moderation being done on the messages
Hi Allen I first want to thank you for that thourough and thoughtful response. I completely appreciate that the challenges you must have with maintaining and combating spam and security threats in an online community are vast and ever changing. I do want to f/u on a couple points and hopefully not beating a dead horse: I do hope you will further consider the issues with blocking all file attachment because as I note, users will just provide links to online storage and sharing platforms (and in fact volunteers in the community regularly ask and suggest for them to do this) and instead of knowing that we are downloading a low risk .xlsx file we could instead be getting something much higher risk not to mention what site we are going to or may get redirected to. I know microsoft can claim that is outside their control and they are 'protecting the community' by not allowing those files, but if you are then pushing people to using and allowing links and web addresses in those same posts, are you really protecting them or pushing and exposing them to something more risky? On a different note, do the algorithms take into consideration the account itself. So does an account with hundreds or thousands of posts and hundreds of likes and solutions get more benefit of the doubt than a basically new account? if not, can it? As for the notification of being on hold and why I totally understand the why has to be limited but getting notified about some basic reasons would be very helpful, like attachment vs link vs language, etc... As for notification I'm sure you are aware of the all the options and I'm sure there are challenges on each but even a PM or a notification in the alert panel would be great. Again thank you for all your work and listening, -MattRe: Can we get more guidance and feedback on the moderation being done on the messages
Hi Allen I first want to thank you for that thourough and thoughtful response. I completely appreciate that the challenges you must have with maintaining and combating spam and security threats in an online community are vast and ever changing. I do want to f/u on a couple points and hopefully not beating a dead horse: I do hope you will further consider the issues with blocking all file attachment because as I note, users will just provide links to online storage and sharing platforms (and in fact volunteers in the community regularly ask and suggest for them to do this) and instead of knowing that we are downloading a low risk .xlsx file we could instead be getting something much higher risk not to mention what site we are going to or may get redirected to. I know microsoft can claim that is outside their control and they are 'protecting the community' by not allowing those files, but if you are then pushing people to using and allowing links and web addresses in those same posts, are you really protecting them or pushing and exposing them to something more risky? On a different note, do the algorithms take into account the account itself. So does an account with hundreds or thousands of posts and hundreds of likes and solutions get more benefit of the doubt than an account that is basically new? If not, can it? As for notification that your message was held and why. I understand you can't give too much information about why to prevent 'gaming the system' but basic information like it having to do with the attachment or language or link would be helpful. As for how to notify, I'm sure you are well aware of the many options and understand a immediate message in that thread may be challenging but what about a message to your PM or an alert in your notifications? In any case, I appreciate you looking into this and sharing what information you are able to share. -MattRe: Excel 365 is UNUSABLE for professional work — performance has catastrophically degraded
Hi RIM_LLC , The first thing you should know is that this is mostly a community forum where community members help other community members. Althogh some microsoft emplyees drop in here time to time this is not meant for nor the right place to report bugs or provide feedback to the microsoft Excel team. If you go into help and click on the feedback button or the contact support, that is where you might want to address some of these items. That said, it is also possible it is your workbook that is having some issue either due to some calculations or corruption. Have you tried to run the Check Performance function (under the 'Review' tab)? Also, have you tried changing from Automatic to Manual Calculations (under Calculations on the Formulas tab)? Do you notice this issue with other workbooks? Have you tried copying the key formulas and pasting them into a new 'clean' workbook? Some other considerations that should be investigated include making sure there are no circular references, bad external references/link/data, minimize the use of conditional formatting, minimize the use of volatile functions (for example the classic ones being: INDIRECT, OFFSET, RAND but can even be INDEX or others if they use abstracted ranges) Another improvement you should consider is updating formulas to actually use the newer more optimized functions like XLOOKUP instead of LOOKUP. You also mention having 67 valuation models which I presume are complicated formulas and you might also find value in using LAMBDA functionality. A number of members here have posted excellent results using 'Thunks', which is a variant of using the LAMBDA function, to improve performance of various complicated formulas. Finally please remember that myself and others here are just volunteers trying to help so although our answers may not be what you want to hear (i.e. we are not MicroSoft saying we are going to fix it), please note we are only trying to help.
