Feb 07 2023 09:12 AM
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
Feb 07 2023 10:08 AM
@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:
and make sure to click that box that says "Show Icon Only"
this is also in the attached file
Feb 07 2023 11:22 AM
Feb 07 2023 01:06 PM
Feb 08 2023 08:22 AM
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.
Feb 08 2023 09:10 AM - edited Feb 08 2023 09:40 AM
@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,"ä")