Top n vs. Others in Excel
Hi all, I'm seeking some help because I'm kind of new to the more intermediate stuff in Excel. I have an Excel table with the following columns: Subcategory in column A, Brand in column B, Region in column C, Year in column D and Values Month in column E. I want to create a PivotTable and a Pivot line chart from this PivotTable that ranks the Top 5 Brands vs. Other Competitors by each region. For added context: There are 5 subcategories, 3 regions and 25 brands. Currently, I've tried grouping the remaining 20 brands as "Other Competitors" vs. the Top 5 brands within a selected region and possibly all regions (when no selection is made). I'm seeking a solution similar to this... Please mind the colours. I will sort those out later. But, the problem that I'm faced with is that upon selection of a region, the PivotTable won't update to the Top 5 brands of a selected region because they've already been grouped. How can I make this more dynamic so that I'm able to show The Top 5 brands vs. Others? Please help. EDIT: My operating system is Windows 10 (64-bit) and I use Excel 365 (Desktop version). For reference, I've attached a link to a sample file. https://1drv.ms/x/c/b2d878e32a062614/IQC1wcnwLICcQasOfnGcwKn0ASjpXp9xQ6rjnOP10Jal5cc?e=HaXEWd Thank you all once again.
PLEASE HELP ME!!! Problem With Excel Formatting Not Working?
PLEASE HELP ME!!! I created an excel workbook for tracking fitness workouts, I record the data such as weight lifted, number of reps, and rest times between sets. I formatted the cells so they express your rest times in terms of minutes and seconds (I used the custom "H:MM" format of time option). But whenever I record the amount of time you spend during cardio exercise, the formatting gets COMPLETELY SCREWED UP because when I type 28:44 (28 minutes and 44 seconds) of cardio duration, it instantly turns into 4:44 (4 minutes and 44 seconds). But if I type 20:00 (20 minutes) of cardio duration, it stays as 20:00 (20 minutes) and it never turns into something else. Why do some of the numbers like 28:44 transform into a complete different number when I enter them? I uploaded 3 screenshots below to better express what I mean.
Index & Match Formula Not Working
In broad terms, I'm on sheet 2 and wanting to show the contents of a cell from sheet 1. Basically, I want whatever is in column C of sheet 1 for the row that has column A in Sheet 1 matching A2 in Sheet 2 AND also has column B in Sheet 1 matching A1 in Sheet 2. I want the contents of that cell to show in Sheet 2. I've made this formula, but clearly I'm missing something (names of sheets have been changed, as the actual ones are people's names): =INDEX('Sheet 1'!C:C,MATCH('Sheet 2'!A1,'Sheet 1'!B:B),MATCH('Sheet 2'!A2,'Sheet 1'!A:A)) Any help would be appreciated.
Bank recon matching
Happy New Year everyone! Can someone please assist me with this to a matching exercise for a bank reconciliation. Tried to do XLOOKUP with REGEXTEST but no luck. Below is the link to the excel file. https://1drv.ms/x/c/80b0e5c8d40a83ef/IQAn2IxiOBDpSJBIIZCfPj3TAexy2EEPYT5VjpDkcpTzpB4?e=S4NEuo
Complex equations with multiple input values
So I'm looking to create a sheet that will do some food math for me. I struggle with excel and I cannot for the life of me figure out how to do this- the online tutorials just make it worse. I'm in vetmed and I know a lot about animal medicine but technology escapes me. I need a sheet that I can input the following info: pt weight (ideal) and illness factor (decided by the DVM on the case) and have it generate the RER (resting energy requirement) for each patient. Once this is generated, I need to be able to take the RER number and have it calculate how many cans per day each pet needs (based on the kcal per can of each diet). Is there a way that I can preprogram all the diets we offer into the sheet (like if someone types in a shorthand code, the kcal per can will populate)? Any insight would be helpful at this point. Thanks!Is there a way to work around Circular reference in Dynamic Array?
Hi! I am trying to convert a table into a dynamic array, but the rows in the table is referring to other column's previous row value, so when I try to convert it into a dynamic array, it'll become a circular reference. Is there a way to work around it? I attached the example excel file to this post for your reference. This is just a simplified example of the actual table I am trying to convert. I tried the "Iterative Calculation" option , the calculation will be correct. But the problem is, the original excel table in my case is very complicated, so even if I set the "Max calculate times" to 10,000, the calculation can not be finished, leaving at least half of the values wrong. My alternative approach is to solve this half dynamically. But I consider it ugly... Leaving only col1 dynamic, and other columns will have to fill down the formula to "row 1048576", which is not pretty. (col1 is actually referring to other places in the real case) Is there a way to work around this and make the table fully dynamic? Thank you! Best Regards, Alan
