Forum Discussion

Gonzo45's avatar
Gonzo45
Copper Contributor
Feb 07, 2023

Excel trending data based on dates & other information.

Good morning/afternoon,   I am trying to find a formula which will not only look through certain dates in the spreadsheet on a separate page, but also identify the type of accident and whether or n...
excel
Gonzo45's avatar
Gonzo45
Copper Contributor
Feb 08, 2023

mtarler 

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.

mtarler's avatar
mtarler
Silver Contributor
Feb 08, 2023

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,"ä")

 

 

 

Resources