formulas and functions
25231 TopicsLoss carry forward for limited years
Hello Excel Community, Excel Version: O365 (Enterprise) I’m running into a mental block trying to model loss carryforward with limited years using a spill formula. Below is a simplified example of what I’m trying to build a formula for. Rules: • A loss from any year can be carried forward for N years, after which it expires and can no longer be used. • In any year with a positive tax liability, if there is any remaining loss from the past N years, you deduct using a FIFO approach (oldest losses used first). • Any unclaimed balance from year N+1 expires, even if it hasn’t been fully used. I’ve found plenty of examples showing loss carryforward without expiration, but I’m struggling to build something that handles both FIFO and expiration in a dynamic array formula. I feel like this must be a solved problem and I’m just missing something obvious. Any help would be greatly appreciated! ------------------------------------------------------------------------------------------12Views0likes0CommentsDisplay Last Value in Column when certain criteria match
Hello all, I am trying to find a way of displaying the last value in a column when certain criteria match... Criteria that need to match would be Design, Colour and Size, so the quantity column would populate with the latest stock quantity: Thanks all! Michael32Views0likes2CommentsConverting date formatted as text to date
I have exported a file from a website which downloads into a csv file. The dates are shown as mm/dd/yyyy and is left aligned which confirms that it is text and not in date format. I want to apply a formula to those birthdates, but of course, the formula doesn't recognize it as a date. I've tried the DATEVALUE function, text to columns, nothing seems to work. Part of the problem might be that I am in Canada, and the format is US, so dates that are 07/20/1944 won't work because Canadian format is dd/mm/yyyy and there is no 20th month....but even the dates like 05/02/1947 won't convert to date format. I've attached the file. It originally downloaded as a csv, but I saved as a workbook hoping that might help....it didn't. Can somone look at this file and come up with a solution that works? Much appreciate any help on this. DennisSolved5.3KViews0likes6CommentsHelp with Excel Sorting
Hello! I am looking for help with sorting data on a sheet with form responses to different excel sheets. I want to filter by column G to different sheets ("Near Miss", "Adverse Event", "Sentinel Event". The data automatically goes to sheet1 via form responses, but I am trying to streamline the data into specific types of events. I'm using office 365. TIA!9Views0likes0CommentsFormula 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!Solved134Views0likes4CommentsTop 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.179Views1like4CommentsSeparating multiple lines in same cell into separate cells
Hi all, I have an urgent query that I have been working on for hours but to no avail. I have a few hundred separate addresses in excel (say in the range A1-A200), arranged over 4-5 lines within each cell. An example is below in Cell A1; A.N. Other 54 Bee Road Anytown Anywhere LE4 6EY Each of the 200 cells that contain the above addresses need each line of the address to be in a separate cell - so in the example above where this address is in Cell A1, I would need the first line (A.N. Other) to present in Cell B1, the 2nd line of the address (54 Bee Road) to present in Cell C1, etc all the way to there the 5th Line (LE4 6EY Postcode) to present in Cell F1. The issue causing the difficulty - Normally, 'Text to Column' should be able to resolve this. However, these addresses have been imported in csv format from another program and so even though the address is arranged over a number of lines, there is no a line break at the end of each line. Due to there being no line break, I am unable to use the 'Text to Columns' functionality to arrange the data into separate cells in the manner that I would like. Apart from me manually going in and copying and pasting line by line (which will take me at least 1-2 days), is there an easy way of doing this? The key problem is linked to there being no line breaks at the end of each line and so I have been unable to do this with 'Text to Columns' functionality. Any help on this would be gratefully appreciated.Solved6.9KViews0likes6CommentsPLEASE 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.77Views0likes1Comment