formulas and functions
25226 TopicsFormula Help for Sorting
Hi all! I am looking to find a formula that will do a multitude of things. One is to sort a column of numbers by the last 2 digits of the values input but by a particular order such as 00-14, 15-29, 30-42. Secondly, once sorted by this grouping, I want to assign it to a particular department based on said sort (Dept 1, 2, 3 and so forth. I have attempted to use the VLookup formula that Google AI gave me to no avail. I received an error. Also with a IF formula and haven't been able to figure out how to make this work. Any help would be greatly appreciated!61Views0likes3CommentsTop 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.147Views1like3CommentsPLEASE 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.66Views0likes1CommentIndex & 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.115Views0likes5CommentsTranslating Morse code in Excel
Hi, I am working on a Morse code translator in Excel with my son. I want to get rid of the FALSE messages in the cell. Can anyone help me? In Morse code the dots are represented with "." and the dashes are represented with "\". The formula we have come up with is: =IF(B3=".\","A",IF(B3="\...","B",IF(B3="\.\.","C",IF(B3="\..","D",IF(B3=".","E",IF(B3="..\.","F",IF(B3="\\.","G")))))))&IF(B3="….","H",IF(B3="..","I",IF(B3=".\\\","J",IF(B3="\.\","K",IF(B3=".\..","L",IF(B3="\\","M",IF(B3="\.","N")))))))&IF(B3="\\\","O",IF(B3=".\\.","P",IF(B3="\\.\","Q",IF(B3=".\.","R",IF(B3="…","S",IF(B3="\","T",IF(B3="..\","U")))))))&IF(B3="…\","V",IF(B3=".\\","W",IF(B3="\..\","X",IF(B3="\.\\","Y",IF(B3="\\..","Z")))))Solved5.2KViews0likes4CommentsBank 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=S4NEuo61Views0likes2CommentsCalculate overlapping hours
Hello, I need to report how many hours a staff member supervised one or more volunteers. For a very simplified example, Volunteer Name Date Start Time End Time Supervisor Fred 1/1/26 1:00pm 3:00pm Lucy Ethel 1/1/26 2:30pm 4:30pm Lucy Here 4 volunteer hours were served, but because there was a 30 minute overlap, Lucy only supervised for 3.5 hours. Is there a way to get Excel to calculate that? To say: look at all the entries with matching date and matching supervisor, and add up non-overlapping time. I'm not expecting this to be possible, but I thought I'd ask. Thanks!Solved414Views1like14CommentsComplex 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!74Views0likes2Comments