User Profile
tusharm10
Brass Contributor
Joined Dec 04, 2020
User Widgets
Recent Discussions
Re: How to create searchable data while filtering automatically ?
Excel's built-in capability gives you what you want. Click the down-arrow in the header row for the column on which you want to filter. You'll see a list of unique values with checkboxes next to them and a field just above the list where you can enter text. As you enter characters in the field, Excel will filter the list of values. It will *also* filter the table itself.2.7KViews0likes0Comments- 1.2KViews0likes0Comments
Re: SUMPRODUCT WITH 2 CONDITIONS
In SUMPRODUCT, you cannot filter using AND the way you did. Instead, you have to include the "filter" inside the function. So, to get the weighted average, use =SUMPRODUCT(--(B2:B30=B33),--(A2:A30=A33),C2:C30,D2:D30)/SUMPRODUCT(--(B2:B30=B33),--(A2:A30=A33),C2:C30) The double negation converts the boolean result into numbers (1 for TRUE and 0 for FALSE). nataleee14601.4KViews0likes2CommentsRe: Highest Lowest price from running price
Another way to do this without code is to enable iterative calculations. File | Options | Formulas tab | Calculation Options section | check the Enable iterative calculation checkbox and also change Maximum Iterations to 1. Then in M10, enter the formula =MAX(L9, M10) dhanu16602.5KViews0likes0Comments- 3KViews0likes0Comments
Re: Count of Date in Pivot Table
I'm not sure how to get what you want from a PivotTable. However, Excel has made several improvements over the past few years so you can do the needful on your own (assuming you have the appropriate version of Excel). Suppose that * your data are in a table as Excel understands it (Insert | Table) and * that the job of interest is in H2. Then the 1st formula will give you a list of unique dates for the job and the 2nd formula will give you a count of the unique dates. =UNIQUE(FILTER(Table1[Date],Table1[Job No]=H2)) =COUNTA(UNIQUE(FILTER(Table1[Date],Table1[Job No]=H2))) Mel_G_0125KViews0likes0CommentsRe: Change workbook name in multiple macros
1. You should look into how to share code as an add-in. That way, there's no proliferation of your code. If you want to fix a bug or enhance a code, you only have to worry about the code in the add-in. If, on the other hand, you are sharing code in a XLSM, there's no way you can find all the workbooks that contain your code, let alone fix all of them. 2. If you must stay with the current architecture, look at VBA objects ThisWorkbook and ActiveWorkbook. When you use those constructs your code will be independent of the name of the workbook containing it as well as independent of the workbook the user is currently working with.1.3KViews0likes1CommentRe: Eliminate multiple older dates from a spreadsheet
Maybe, I'm missing something but... If you can query the database, the below SQL will give you what you want. You can also use drag-and-drop in MS Query to get Excel to build the query for you. select id, max(date_of_interest) from my_table group by id Tracy72126.4KViews0likes1CommentRe: Formula keeps incrementing when inserting rows
I suspect we've discussed this as much as any of us want. 🙂 So, this will be my last post in this discussion. I also assume the version of Excel in use supports the necessary formulas and features. The idea behind the below is that I've never been a fan of asking someone to "make space at the top" to enter data, though, sad to say, I've done it. But with the newer capabilities of Excel, we can avoid that kind of UI/UX. With the dates in column A and the readings in column 😧 For a 7 day average for the most recent 7 days, use =AVERAGEIFS(D:D,A:A,">="&MAX(A:A)-7) To get a list of the latest 7 days and the average reading per day, I'd use the dynamic array formulas: In N4: =MAX(A:A)-SEQUENCE(7,,0) In O4: =AVERAGEIFS(D:D,A:A,N4#) wcstarks7.1KViews0likes0CommentsRe: Index match Function
* What happens if there are 3 or more vendors that match the minimum value? * You will be much better off if you can reorganize the data to the format place vendor value usa vendor-1 4000 usa vendor-2 2000 etc. If you can do that, there are fairly straightforward approaches to consider. swstr5103.1KViews0likes0CommentsRe: Formula keeps incrementing when inserting rows
The data start in row 3, correct? Row 2 is empty except for the formula. Also, if you want a 7-day average, the formula should reference only 7 days of data, not go all the way to D16, which would give you a 14 (or 15) day average. In any case, to get 7 days of data starting with D3 and not have to adjust the formula when you push data in D3 down each day, use =AVERAGE(OFFSET(D2,1,0,7,1)) wcstarks7.4KViews0likes14Comments
Recent Blog Articles
No content to show