Excel trending data based on dates & other information.

Copper Contributor

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 not it occurred the previous year based on the compare dates.

Is there a way to get the formula to do all of this and it will continue to change this information based on the current date and the same date from the previous year. Also, it will need to indicate based on these date and other parameters whether or not there are more, fewer, or the same number of accidents from the current, and previous years accident numbers. That might be asking a lot, thank you. Please see the example excel sheet to base the formula off of, again thank you in advance, and there is a little more information in the excel attached.

Gonzo45

5 Replies

@Gonzo45  NOTE: I converted your tables to be "Formatted as a Table" because this well be easier to read and expands as the table expands.

Try this formula but there are many variations you can use.

=SWITCH(LET(c,COUNTIFS(Table1[Accident Date],$I$2,Table1[Accident Class],"A",Table1[Accident Type],$B$1)-COUNTIFS(Table1[Accident Date],EDATE($I$2,-12),Table1[Accident Class],"A",Table1[Accident Type],$B$1),
IF(c<>0,c/ABS(c),0)),
1,"↑",
0,"↔",
-1,"↓")

so what I do is first COUNTIFS for conditions for TODAY and the - COUNTIFS for 1 year ago (I used EDATE( today, -12) to get same date 12 months ago)

now you could use a couple of IF statements but I chose to check if it is 0 or if not then divide by ABS value of itself to get a positive (1) or negative (-1) output and then use SWITCH for 1, 0, -1

That all said, it gives your text arrows as the output but NOT color formatting.  Color formatting must be done using Conditional Formatting.  That said, there is a conditional formatting option to use Icon Sets that would get you really close to what you want without that whole SWITCH and ABS() business.  Just return the value like this:

=COUNTIFS(Table1[Accident Date],$I$2,Table1[Accident Class],"A",Table1[Accident Type],$B$1)-COUNTIFS(Table1[Accident Date],EDATE($I$2,-12),Table1[Accident Class],"A",Table1[Accident Type],$B$1)

and use conditional formatting as shown here:

mtarler_0-1675792994517.png

and make sure to click that box that says "Show Icon Only"

this is also in the attached file

MTARLER,
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
The formula can easily be updated to countifs a range of dates but I do not understand what range to use. You say "comparing year to date (YTD) comparisons from the previous YTD time (7 Feb 2023 - to - 7 Feb 2022 for example) " but then in other spot you mention Fiscal Year and insinuate elsewhere Calendar Year.
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.

@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.

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