Forum Widgets
Latest Discussions
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. Excel Time 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. '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
Conditional Formatting multiple conditions
Hey all! I need some help writing a formula for some conditional formatting I'm looking to do for a contact log. Basically, I want to highlight dated cells if the interaction with a contact is -14 days and -21 days from today's date. I've figured that out and am using the formula =DATEDIF($E7,$F$1,"D")>14 and =DATEDIF($E7,$F$1,"D")>21 However I also want to run conditional formatting so that the cells only highlight when the Contact info in the log is a unique value, and this is what I'm currently struggling with. For Confidentiality, I can't show the values populated in the Contact Column, but I can show an example of my table's columns if that helps. So in essence, I want the date formatting to run only if the contact value is a unique value, so that it is not flagging an old log of an interaction and only flagging the most recent interaction with that contact name if it is dated past 2-3 weeks. Is there anything I can build that will operate in this way? Thanks!
