Forum Discussion
days since last accident
=$G$2-LARGE(IF((Table1[Fatality]=1)*(Table1[Accident type]=H$1),Table1[Accident date]),1)You can try this formula which takes the types of accidents into account. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021. To simplify the solution i'd recommend to combine all four sheets into one. In this example the data is in a dynamic table and therefore all added records are included in the calculation automatically.
=G2-LARGE(IF(H3:H22=1,E3:E22),1)Maybe with this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.
My apologies for taking so long to reply, I tried this and it seems I failed to mention another piece to this request. The chart I am using is on a separate sheet from the dates, not to mention there are numerous sheets (4) where the different dates I have are maintained, which I may be able to reduce onto one sheet in another location to use easier, but that may take a little work on my part later. Also the dates are for specific types of accidents (PMV-2, PMV-4, ACV, AMV, NTV) vehicles. These are acronyms for vehicles so don't let it confuse you because they don't seem to make sense otherwise.
If possible I would want excel to look at a date on each of the 4 sheets and the type vehile acronyms and determine if they meet having a fatality then use the most recent date to make the last traffic fatality "days since last" event and input the number in the box automatically to take out one of us here making errors or forgetting to update that bit of information. Sorry to make it more difficult, and I thank you for any assistance you can give.
Gonzo45
=$G$2-LARGE(IF((Table1[Fatality]=1)*(Table1[Accident type]=H$1),Table1[Accident date]),1)You can try this formula which takes the types of accidents into account. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021. To simplify the solution i'd recommend to combine all four sheets into one. In this example the data is in a dynamic table and therefore all added records are included in the calculation automatically.
- Thank you all for your responses, you have helped me tremendously. I am trying to take some courses through Udemy online in order to learn excel a little better (actually a lot), time will tell.