Benefit of asterisk vs nested IF in multiple IF criteria formulae

[kcmuppet]

Hello,

I'm curious about whether there is a benefit in using a "*" to effectively separate / multiply criteria in IF functions vs nesting.

For example...

=INDEX(Table1[Color],MATCH(MAX( IF ( Table1[Item]="T-shirt" , IF (Table1[Size]="Large" , Table1[Time] ) ) ),Table1[Time],0))
vs
=INDEX(Table1[Color],MATCH(MAX( IF ( (Table1[Item]="T-shirt") * (Table1[Size]="Large"), Table1[Time] ) ) ,Table1[Time],0))

...seem to produce the same result, but is one any more efficient that another? Or is there some other relative benefit?

I have a table of ~8k rows and 45+ columns to search and ~35 of these formulae to populate another table, and sometimes 2-3 of these themselves subject to IF criteria in the same formula.
I have to stick to things that work for Excel 2010 so can't later alternatives.

 

[jasonb75]

Don't think that there is any performance difference (if there is it would be negligible), but you could do away with IF altogether

=INDEX(Table1[Color],MATCH(MAX( (Table1[Item]="T-shirt") * (Table1[Size]="Large")* Table1[Time] ) ,Table1[Time],0))

In many cases, formulas without IF don't need to be array confirmed.

 

[kcmuppet]

Don't think that there is any performance difference...

Thanks for pointing out how to use a shorter formula in the Index Match example. That will certainly help me. In the following kind of formula though, I can think only that it's bit easier to not lose track of closing nested IF brackets while you edit the formula (using line feed with ALT Enter)...

=MIN(IF(
(tbl_Events[Name]=[@[Name]])*
(tbl_Events[Event]="Type 1")*
(tbl_Events[Event date]>=ReportStartDate),
tbl_Events[Event date]))

 

[jasonb75]

For something like that, I would consider using AGGREGATE which will prevent erroneous zero results if and when there are no rows meeting the criteria.

=AGGREGATE(15,6,tbl_Events[Event date]/
(tbl_Events[Name]=[@[Name]])/
(tbl_Events[Event]="Type 1")/
(tbl_Events[Event date]>=ReportStartDate),1)

You could still do it without using IF with MIN using

=MIN(
(tbl_Events[Name]=[@[Name]])*
(tbl_Events[Event]="Type 1")*
(tbl_Events[Event date]>=ReportStartDate)*
tbl_Events[Event date])

As you said, a key benefit is not losing track of the closing parenthesis.

 

[kcmuppet]

Well, thanks again, for the AGGREGATE tip instead of MIN. That's exactly a situation a I have with one of my formulae, where I have to wrap a bunch of IF's together to see if it will return something expected first, and thinking about it, I'm not sure it quite covers all bases).

Here, what I was aiming for was "Find me the date of the next or last 'Event Type A' for this person", but was coming up against exactly that erroneous zero from the MAX function, so kind of rigged it by adding 1:

=IF([@[Count of Event Type A so far for this person]]
=MAX(IF(
([Name]=[@[Name]])*
([Event]="Event Type A"),
[Count of Event Type A so far for this person])),
SMALL(IF(
([Event]="Event Type A")*
([Name]=[@[Name]]),
[Event Date]),[@[Count of Event Type A so far for this person]]]),
SMALL(IF(
([Event]="Event Type A")*
([Name]=[@[Name]]),
[Event Date]),[@[Count of Event Type A so far for this person]]+1)
)

Again, I'm using the asterisks approach to just help me separate the logical criteria as I find it easier than finding & fixing parentheses issues of the formula doesn't work/ pass the syntax check.

Nevertheless, I have a weak understanding of it's use here as a mathematical or multiplying operator - if that is indeed its actual function. And now seeing that "/" operator in the AGGREGATE example makes more sense to me as an effective separator between filters (conceptually dividing the results). But they are indeed being used as mathematically as multipliers and dividers?

 

[jasonb75]

But they are indeed being used as mathematically as multipliers and dividers?

They multiply or divide logic. From a mathematical point of view, TRUE has a value of 1 and FALSE has a value of 0. Multiplying or dividing any value by 1 retains the original value. Multiplying by 0 always returns 0 (which is the cause of the erroneous 0 result in such formulas). Dividing by 0 is not mathematically possible so it returns an error value or #DIV/0!. Unlike other functions, AGGREGATE has an option to ignore such errors and only look at the remaining values in the array (returning a #NUM! error when there are no valid results.

Looking at the formula you have just posted, I think that this one needs a different approach again. If I'm following correctly, you're trying to keep a running count of matching events? If so then the task is much easier than you might think. Just drop this formula into the first cell of the [Count of Event Type A so far for this person] column and see if the results are as expected.

=COUNTIFS([Event],"Event Type A",[Name],[@[Name]][Event Date],"<="&[@[Event Date]])

I've cut it back to one line as I've noticed previously that table names with spaces can sometimes copy incorrectly when there are line breaks.

 

[kcmuppet]

...If I'm following correctly, you're trying to keep a running count of matching events? If so then the task is much easier than you might think....

Actually I'm trying to keep a running total of 'this type of event' since the last event of Type A and before the next event of Type A.

(...as an interim step to establishing the days between each event and the preceding Event type A, and then comparing vs a target. )

My data is a record of events a bit like this:

...so I was trying to use the MAX and SMALL to try to give boundaries to the count.

 

[jasonb75]

I think that this should do it,

=IFERROR(1/(1/([@[Event Date]]-MAXIFS([Event Date],[Event],"Event Type A",[Name],[@[Name]][Event Date],"<"&[@[Event Date]]))),"")

 

[kcmuppet]

Yes indeed (with missing comma added between [@[Name]] and [Event Date])...

=IFERROR(1/(1/([@[Event Date]]-MAXIFS([Event Date],[Event],"Event Type A",[Name],[@[Name]],[Event Date],"<"&[@[Event Date]]))),"")

...does provide the expected result, but MAXIFS doesn't work for Excel 2010 (which is why I was wrestling with the * vs nested IFs)

Would you minding helping me with the logical steps.

I don't understand the "1 divided by 1 divided by the maximum..."

 

[jasonb75]

but MAXIFS doesn't work for Excel 2010

If you regularly need suggestions to work with older versions then it would be a good idea to change the version shown on your account details to show the oldest version needed, otherwise people will base there suggestions on 365 as it currently shows. For occasional suggestions to work with an older version be sure to clearly state that this is needed in the first post of each thread. I thought that part of the reason for the methods you were trying was that you were not aware of some of the newer functions. (also, your signature shows 2016 so I would suggest removing that as well to reduce confusion).

The 1/(1/ part is used to cause intentional errors when the result is 0 so that the row can be blanked out. It's really just a way of doing =IF(something>0,something,nothing) without repeating whole sections of formula.

I was just looking at your screen capture again and don't think it is ideal. Name 12 has no previous Type A so the formula would default to Dec 31 1899 as the starting date and give some very high numbers for the difference.

Taking that into consideration, along with the need for a 2010 compatible formula, I think that this should work.

=IF([@[Event]]="Event Type A","",IFERROR([@[Event Date]]-1/(1/AGGREGATE(14,6,[Event Date]/([Event]="Event Type A")/([Name]=[@[Name]])/([Event Date],<=[@[Event Date]]),1)),""))