User Profile
m_tarler
Bronze Contributor
Joined 3 years ago
User Widgets
Recent Discussions
Re: Cannot Create a Scatter Plot with Two Variables
I think you want a line chart (you can choose to not show the line and only the data points). Then each column will be plotted as its own series. A scatter plot is Y plotted against X so excel is using 1 of the columns for the x-axis and the other is on the y-axis and hence 1 series.Re: Percentage HELP!
ok in the web version the buttons are 'reversed': step 1: highlight cells to be formatted step 2: click on the % button (shown in image above) step 3: click on the increase decimal places button (the one on the right in the image above with the arrow pointing right) step 4: enter your value (e.g. 0.0929) and is will show as 9.29% I have formatted cells M4:M20 this way for you to try out.Re: Checkboxes in Microsoft Excel
I am guessing you are using a Form Control or Active X control. If you have Excel 365 there is a new checkbox insert option on the insert menu. This new checkbox inserts the checkbox into the corresponding cells and the Value (T/F) is in that cell. So just highlight the cells you want to have a checkbox in and click that option.Re: Fill dates series of Thursdays, Fridays and Saturdays with fill handle
I don't know if that ever worked but there are a few options. As you mentioned you could create a formula but alternatively you could use 12/13, 12/14, 12/15 and 4 blank rows then drag down and that should give you the 3 days of interest separated by 4 blanks all the way down. Then while that column is still highlighted select 'Remove Duplicates' to get rid of all the blanks (except 1 which you can manually delete).Re: How should I write this Process?
I agree with mathetes. At the very least we may help you do whatever you are ultimately trying to do better. but i believe you want a function to give you an array of rand numbers for each row/person where the 'chances' are the number of elements in the array for that row. Basically you want RANDARRAY() =RANDARRAY(1,C5,1,100,1) I'm not sure what you are hoping for with the red and blue. On another note, the problem with random numbers in excel is that they will change every time you do anything on the sheet. so if you want to document the result you will need to take a screen shot or copy and then paste values only to lock the valuesRe: How can I display negative values for time in calculation results
To build on Harun's answer for what I think is you specific case: =LET(timediff, $B$1 - A1:A100, IF(timediff<0,"-","")& TEXT(ABS(timediff),"[h]:mm:ss.0")) where B1 is you fixed time value and A1:A100 is your range of times this will unfortunately produce TEXT values (i.e. you will NOT be able to math with them)Re: How to count duplicates ... sort of ...
So I think the other answers so far don't do what you want because I think you want the formula to be smart enough to see "Black Screen" as the SAME as "No Boot - Black Screen" which makes this problem expodentially harder! My first try was to make small tweak to Oliver's solution to allow wild card charaters on each side of the search but given multiple entries with partial matches like that make it hard to get a real value (i.e. each partial match is a fraction of a normal match but those fractions could add up to more than 1 so unclear how many partial matches there are. In the attached I show my new formula followed by all the 'other' formulas including the last one being this modification of Oliver's. I say this in case one of my colleagues here can find a more clever way to use that to get the correct answer. That all said my solution is much more complicated: =LET(in, CHOOSECOLS(A2:F10,3,6), uniqStar, LAMBDA(arr,LET(g,--ISNUMBER(SEARCH("*"&arr&"*",TRANSPOSE(arr))),corr,--(g+TRANSPOSE(g)>0),counts,MMULT(corr,EXPAND(1,ROWS(corr),1,1)),SUMPRODUCT(1/counts))), REDUCE(0,UNIQUE(TAKE(in,,1)),LAMBDA(p,q,p+uniqStar(FILTER(TAKE(in,,-1),TAKE(in,,1)=q))))) so line 1 just pulls the 2 columns of interest line 2 creates a LAMBDA function that will compare each value in the array to each other value in the array and find partial matches and return a result of unique matches based on including partial matches. This is still not perfect (see example below) line 3 then takes each Unique Serial Number and feed it into the new 'uniqStar' function and sums the result in case the file doesn't attach right here is a screenshot: so in the first example I added an extra line SPEA-1040 to further test the examples. in col G I have quick manual check on what I considered unique or if not unique how many dups. In col I you can see the results of this new formula correctly finds 6 truely unique cases. The other formulas find 8 strictly unique cases and the last column M found 7 because of the 2 cases with partial matches each being 0.5 adding to 1 more. In the last example you can see the partial matches are "Black Screen", "No Boot" , and "No Boot - Black Screen" and because the partial matches aren't 1 for 1 (i.e. "Black Screen" and "No Boot" both match "No Boot - Black Screen" but not each other while "No Boot - Black Screen" finds a partial match to each) you get a fraction added.Re: merge cells
can you be more specific about what doesn't work and why? as Oliver suggested a simple & between terms should work but CONCATENATE() should work on older versions On newer excel CONCAT() and TEXTJOIN() should work but each of these solution will need to be done and then copied/filled down if you are interested in an array function to do all rows at once you could use BYROW() helper function or interestingly the old CONCATENATE() might work because at least in the newer excel you can use this (until they fully obsolete it): =CONCATENATE(B2:B3,C2:C3,D2:D3,E2:E3,F2:F3,G2:G3,H2:H3)Re: Need help with SORTBY function.
that is probably because the sortby() function needs an array for the sortby parameters (not just a column number). So you need to filter the table then pass the full column 3, column 4, and column 5 to the sortby parts. this is perfect time to use the LET() statement: =LET(in, A1:E17, a, FILTER(in,CHOOSECOLS(in,2)="M"), VSTACK( TAKE(in,1), SORTBY(a,CHOOSECOLS(a,3),-1,CHOOSECOLS(a,4),-1,CHOOSECOLS(a,5),-1)) ) so you can pick whatever range you want to use for line 1 line 2 performs the FILTER (in this case filter the second column for "M" line 3 is the output and on line 4 it will return the header row and line 5 does the sort notice on line 5 each sortby is passing the full column of corresponding data.Re: I am making Thursday night NFL games only
hard to say because I open the file and see this: you did say starting on week 3 and it looks like you started on week 2 and since the values are the same but shifted I'm guessing that is your problem because it is shifted and then that last row is pointing at non-existent cellsRe: Have you ever wished Excel could calculate faster — maybe even use your GPU?
There are lots of things in a spreadsheet that can cause it to slow down. I'm not saying there is a cure for your issue because Excel is NOT a database solution but here are a few things to check and try: a) use the (new) built in feature called Check Performance (go to 'Review' tab). this feature will find numerous things in a workbook that can be causing slow downs b) avoid use of excessive conditional formatting. that could be rules applied to huge ranges or just tons of rules c) avoid use of 'volatile' functions. These include functions like INDIRECT, OFFSET, RAND, etc... d) MAKE use of new functions like XLOOKUP and XMATCH instead of the older versions as they have improved performance (and functionality) on them e) MAKE use of array calculations instead of individual calculations that are then filled down/right Additional tips are much harder without seeing the actual book. There are many threads here dedicated to or at least include discussion about improved performance. For complicated formulas, it appears there can be significant improvement using a concept of THUNKS (a form of using LAMBDA functions) hope that helps
