Forum Discussion
Excel trending data based on dates & other information.
This is an excellent formula thank you. My only problem now is that, while comparing year to date (YTD) comparisons from the previous YTD time (7 Feb 2023 - to - 7 Feb 2022 for example) I would like for it to be able to calculate the accident types and keep the total. So for instance, if 5 Feb 2023 there were a PMV-4 accident, then again on the 7th and 8th of Feb 2023, it would compare these dates as they come (5th. 7th & 8th of Feb 2022 vs Feb 2023) and then it would calculate those PMV-4's (3) and keep them in the total count for the YTD and through to the end of the fiscal year. This would indicate a trend upward if say, this FY YTD 7 Feb 2023, instead of having an accident in a PMV-4 on the 5th. 7th & 8th we only had a PMV-4 on the 7th of Feb 2023, it would show a down arrow since there wwere less PMV-4 accidents then this time last year (as of 7 Feb 2023 ↓)
I hope this makes sense to you, it seems Ok to me, but I know what I'm trying to say to begin with... can't confuse myself that easy, I think...
Thank you very much though, so far, you've gotten me further than I EVER could have by myself. I didn't even realize I could do conditional formatting like that to begin with much less what the formulas were supposed to be.
Gonzo45
You also mention you like "the" formula and that you like the conditional formatting option. When you answer the above please also note if the formula should be based on using the arrow CHARACTERS or just output the value and use conditional formatting.
My apologies, we only use Fiscal Year here throughout for everything. The dates correspond to October (Month 1) thru to September the following calendar year (Month 12) Quarters are Oct-Dec= Qtr 1, Jan-Mar= Qtr 2, Apr-Jun= Qtr 3, Jul-Sep= Qtr 4.
I used calendar year reference above only to showing crossing over into the new year, not for any tracking purposes in the tracker.
I like the conditional formatting, it just is not set up for the colors of arrows I need (up arrow is red (Bad), down is green (Good), lateral is orange (No Change)). We've been using this color scheme for years so do not want to change it, to prevent issues, I believe in the principle of K.I.S.S. (Keep It Stupid Simple). If possible, the custom formatting CHARACTERS would likely work better, unless the conditional formatting colors you used can be changed, I do prefer the thicker arrows overall.
I added in the attached excel document the other rows to provide you all info for what we track. I do track the Quarters as well if that is something you can use, not sure though. I also added a little more information in the excel sheet page 1 for an example, I hope it doesn't confuse as I'm not too versed in explaining the processes for excel, terminology-wise. Thanks for your patience and help.
Gonzo45 ok i updated the formula to tally from the prior Oct 1 to that date and compare to the same region 1 year prior. I also updated the arrows to use Wingdings 3 arrows and conditional formatting on that cell to match. The 0 arrow is only pointing right and not both ways. I also have a cell with a variety of arrow options.
=SWITCH( SIGN( COUNTIFS( Data[Accident Date],"<="&$I$2, Data[Accident Date],">="&DATE(YEAR($I$2)-(MONTH($I$2)<10),10,1), Data[Accident Class],"A", Data[Accident Type],$B$1) -COUNTIFS( Data[Accident Date],"<="&EDATE($I$2,-12), Data[Accident Date],">="&DATE(YEAR($I$2)-1-(MONTH($I$2)<10),10,1), Data[Accident Class],"A", Data[Accident Type],$B$1) ), 1,"ã", 0,"â", -1,"ä")