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.

 

[kcmuppet]

Ahhh. Thanks for the explanation. I'm having trouble with AGGREGATE solutions. Excel doesn't accept that last formula in column I below, and when I tried to create an AGGREGATE solution to replace the MAXIFS and MAX(IF I'm not getting sensible results either.

I've included here some of the actual data instead of the screen grab. You're quite right through that there are sometimes Events in the data that are not preceded by an Event Type A (event though they should be irl), so I will need to have to deal with those errors.

Scratch Book1 (version 1).xlsb
	B	C	D	E	F	G	H	I
22	Name	Event Date	Event	Date of latest Type A	MAXIFS Date of first of this type of Event for this Name	MAX ( IF Date of first of this type of Event for this Name	AGGREGATE Date of first of this type of Event for this Name	AGGREGATE This Event type since the last Type A, before the next Type A
23	6106691797	01 Mar 18	Event Type A	01 Mar 18	01 Mar 18	01 Mar 18
24	6106691797	01 Mar 18	Event Type D	01 Mar 18	01 Mar 18	01 Mar 18
25	6106691797	23 Mar 18	Event Type A	23 Mar 18	23 Mar 18	23 Mar 18	22 Jan 00
26	6106691797	23 Mar 18	Event Type D	23 Mar 18	23 Mar 18	23 Mar 18	22 Jan 00
27	6106691797	28 Mar 18	Event Type A	28 Mar 18	28 Mar 18	28 Mar 18	27 Jan 00
28	6106691797	28 Mar 18	Event Type D	28 Mar 18	28 Mar 18	28 Mar 18	27 Jan 00
29	6106691797	03 Apr 18	Event Type C	28 Mar 18	03 Apr 18	03 Apr 18
30	6106691797	04 Apr 18	Event Type B	28 Mar 18	04 Apr 18	04 Apr 18
31	6106691797	12 Jul 18	Event Type A	12 Jul 18	12 Jul 18	12 Jul 18	12 May 00
32	6106691797	12 Jul 18	Event Type D	12 Jul 18	12 Jul 18	12 Jul 18	12 May 00
33	6106691797	16 Jul 18	Event Type C	12 Jul 18	16 Jul 18	16 Jul 18	13 Apr 00
34	6106691797	18 Jul 18	Event Type B	12 Jul 18	18 Jul 18	18 Jul 18	14 Apr 00
35	6106691797	20 Jul 18	Event Type C	12 Jul 18	20 Jul 18	20 Jul 18	17 Apr 00
36	6106691797	25 Jul 18	Event Type B	12 Jul 18	25 Jul 18	25 Jul 18	21 Apr 00
37	6106691797	27 Jul 18	Event Type C	12 Jul 18	27 Jul 18	27 Jul 18	24 Apr 00
38	6106691797	08 Aug 18	Event Type C	12 Jul 18	08 Aug 18	08 Aug 18	06 May 00
39	6106691797	08 Aug 18	Event Type C	12 Jul 18	08 Aug 18	08 Aug 18	06 May 00
Sheet1

Cell Formulas
Range	Formula
F23:F39	F23	=IFERROR(1/(1/([@[Event Date]]-MAXIFS([Event],[Event],[@Event],[Name],[@Name],[Event Date],"<"&[@[Event Date]]))),"")
G23:G39	G23	=IFERROR(1/(1/([@[Event Date]]-MAX(IF( ([Event]=[@Event])* ([Name]=[@Name])* ([Event Date]<[@[Event Date]]), [Event])) )),"")
H23:H39	H23	=IFERROR([@[Event Date]]-1/(1/ AGGREGATE(15,6,[Event Date] /([Event]=[@Event]) /([Name]=[@Name]) /([Event Date]<[@[Event Date]]),1) ),"")

Thanks for the advice to update signature and profile. I guess the comment in my first post was easy to miss/confuse.

 

[jasonb75]

I guess the comment in my first post was easy to miss

Yep, I checked to see if you had mentioned it anywhere before I said about it and still missed it

Looking at your data grab above, column H just needs to be reformatted to number / general. When you perform calculations on dates, excel incorrectly thinks it would be helpful to show the results as dates too.

I'm looking at your headers and getting lost as to what you actually mean with them, could you enter the expected results the same as you did with the earlier screen capture, then repost it as XL2BB so that I can test the formulas on the proper sample

 

[kcmuppet]

Hi thanks for bearing with me

Here is a version with last two column showing the results I'm trying to get using AGGREGATE in columns G and H manually entered:

Scratch Book2.xlsx
	B	C	D	E	F	G	H
22	Name	Event Date	Event	Date of latest Type A	MAX ( IF Date of first of this type of Event for this Name	Same as Column G using AGGREGATE	Running count of this type of event for this Name since the last Type A using AGGREGATE
23	6106691797	01 Mar 18	Type A	01 Mar 18	01 Mar 18	01 Mar 18	1
24	6106691797	01 Mar 18	Type D	01 Mar 18	01 Mar 18	01 Mar 18	1
25	6106691797	23 Mar 18	Type A	23 Mar 18	23 Mar 18	23 Mar 18	1
26	6106691797	23 Mar 18	Type D	23 Mar 18	23 Mar 18	23 Mar 18	1
27	6106691797	28 Mar 18	Type A	28 Mar 18	28 Mar 18	28 Mar 18	1
28	6106691797	28 Mar 18	Type D	28 Mar 18	28 Mar 18	28 Mar 18	1
29	6106691797	03 Apr 18	Type C	28 Mar 18	03 Apr 18	03 Apr 18	1
30	6106691797	04 Apr 18	Type B	28 Mar 18	04 Apr 18	04 Apr 18	1
31	6106691797	12 Jul 18	Type A	12 Jul 18	12 Jul 18	12 Jul 18	1
32	6106691797	12 Jul 18	Type D	12 Jul 18	12 Jul 18	12 Jul 18	1
33	6106691797	16 Jul 18	Type C	12 Jul 18	16 Jul 18	16 Jul 18	1
34	6106691797	18 Jul 18	Type B	12 Jul 18	18 Jul 18	18 Jul 18	1
35	6106691797	20 Jul 18	Type C	12 Jul 18	20 Jul 18	20 Jul 18	2
36	6106691797	25 Jul 18	Type B	12 Jul 18	25 Jul 18	25 Jul 18	2
37	6106691797	27 Jul 18	Type C	12 Jul 18	27 Jul 18	27 Jul 18	3
38	6106691797	08 Aug 18	Type C	12 Jul 18	08 Aug 18	08 Aug 18	4
39	6106691797	08 Aug 18	Type C	12 Jul 18	08 Aug 18	08 Aug 18	5
Sheet1

Cell Formulas
Range	Formula
F23:F39	F23	=IFERROR(1/(1/([@[Event Date]]-MAX(IF(([Event]=[@Event])*([Name]=[@Name])*([Event Date]<[@[Event Date]]),[Event])))),"")

 

[kcmuppet]

With sincere apologies the previously posted outputs were wrong!

Below is a corrected version with last two column showing the results I'm trying to get using AGGREGATE in columns G and H manually entered. It also seems that what I had previously in F "Date of first of this type of Event for this Name" was also wrong. I left the wrong formulae producing the right result in the first row

Scratch Book2.xlsx
	B	C	D	E	F	G	H	I
22	Name	Event Date	Event	Date of latest Type A	Date of first of this type of Event for this Name	Date of first of this type of Event for this Name since the last Type A using MAX( IF	Same as Column G using AGGREGATE	Running count of this type of event for this Name since the last Type A using AGGREGATE
23	6106691797	01 Mar 18	Type A	01 Mar 18	01 Mar 18	01 Mar 18	01 Mar 18	1
24	6106691797	01 Mar 18	Type D	01 Mar 18	01 Mar 18	01 Mar 18	01 Mar 18	1
25	6106691797	23 Mar 18	Type A	23 Mar 18	01 Mar 18	23 Mar 18	23 Mar 18	1
26	6106691797	23 Mar 18	Type D	23 Mar 18	01 Mar 18	24 Mar 18	24 Mar 18	1
27	6106691797	28 Mar 18	Type A	28 Mar 18	01 Mar 18	28 Mar 18	28 Mar 18	1
28	6106691797	28 Mar 18	Type D	28 Mar 18	01 Mar 18	28 Mar 18	28 Mar 18	1
29	6106691797	03 Apr 18	Type C	28 Mar 18	03 Apr 18	28 Mar 18	28 Mar 18	1
30	6106691797	04 Apr 18	Type B	28 Mar 18	04 Apr 18	28 Mar 18	28 Mar 18	1
31	6106691797	12 Jul 18	Type A	12 Jul 18	01 Mar 18	12 Jul 18	12 Jul 18	1
32	6106691797	12 Jul 18	Type D	12 Jul 18	01 Mar 18	12 Jul 18	12 Jul 18	1
33	6106691797	16 Jul 18	Type C	12 Jul 18	03 Apr 18	12 Jul 18	12 Jul 18	1
34	6106691797	18 Jul 18	Type B	12 Jul 18	04 Apr 18	18 Jul 18	18 Jul 18	1
35	6106691797	20 Jul 18	Type C	12 Jul 18	03 Apr 18	20 Jul 18	20 Jul 18	2
36	6106691797	25 Jul 18	Type B	12 Jul 18	04 Apr 18	18 Jul 18	18 Jul 18	2
37	6106691797	27 Jul 18	Type C	12 Jul 18	03 Apr 18	20 Jul 18	20 Jul 18	3
38	6106691797	08 Aug 18	Type C	12 Jul 18	03 Apr 18	20 Jul 18	20 Jul 18	4
39	6106691797	08 Aug 18	Type C	12 Jul 18	03 Apr 18	20 Jul 18	20 Jul 18	5
Sheet1

Cell Formulas
Range	Formula
F23	F23	=IFERROR(1/(1/([@[Event Date]]-MAX(IF(([Event]=[@Event])*([Name]=[@Name])*([Event Date]<[@[Event Date]]),[Event])))),"")
G23,G27:G28	G23	=IFERROR(1/(1/([@[Event Date]]- MAX(IF( ([Event]=[@Event])* ([Name]=[@Name])* ([Event Date]<[@[Event Date]])* ([Event Date]<[@[Date of latest Type A]]), [Event])))),"")

 

[kcmuppet]

I'm making progress and now have some of the results I'm looking for using the AGGREGATE function suggested:

I ended up using AGGREGATE's options 14 & 15 LARGE & SMALL because when I tried MIN & MAX with the same criteria, I get a #Value errors usinge the same criteria, which I don't understand.

I also don't understand though why my count isn't working in column H.

Scratch Book2.xlsx
	B	C	D	E	F	G	H	I
22	Name	Event Date	Event	Date of latest Type A so far	Date of first of this type of Event for this Name	Date of first of this type of Event for this Name since the last Type A	Running count of this type of event for this Name since the last Type A using AGGREGATE	Expected value for Column H
23	6106691797	01 Mar 18	Event Type A	01 Mar 18	01 Mar 18	01 Mar 18	1	1
24	6106691797	01 Mar 18	Event Type D	01 Mar 18	01 Mar 18	01 Mar 18	#VALUE!	1
25	6106691797	23 Mar 18	Event Type A	23 Mar 18	01 Mar 18	23 Mar 18	1	1
26	6106691797	23 Mar 18	Event Type D	23 Mar 18	01 Mar 18	23 Mar 18	#VALUE!	1
27	6106691797	28 Mar 18	Event Type A	28 Mar 18	01 Mar 18	28 Mar 18	1	1
28	6106691797	28 Mar 18	Event Type D	28 Mar 18	01 Mar 18	28 Mar 18	#VALUE!	1
29	6106691797	03 Apr 18	Event Type C	28 Mar 18	03 Apr 18	03 Apr 18	#VALUE!	1
30	6106691797	04 Apr 18	Event Type B	28 Mar 18	04 Apr 18	04 Apr 18	#VALUE!	1
31	6106691797	12 Jul 18	Event Type A	12 Jul 18	01 Mar 18	12 Jul 18	1	1
32	6106691797	12 Jul 18	Event Type D	12 Jul 18	01 Mar 18	12 Jul 18	#VALUE!	1
33	6106691797	16 Jul 18	Event Type C	12 Jul 18	03 Apr 18	16 Jul 18	#VALUE!	1
34	6106691797	18 Jul 18	Event Type B	12 Jul 18	04 Apr 18	18 Jul 18	#VALUE!	1
35	6106691797	20 Jul 18	Event Type C	12 Jul 18	03 Apr 18	16 Jul 18	#VALUE!	2
36	6106691797	25 Jul 18	Event Type B	12 Jul 18	04 Apr 18	18 Jul 18	#VALUE!	2
37	6106691797	27 Jul 18	Event Type C	12 Jul 18	03 Apr 18	16 Jul 18	#VALUE!	3
38	6106691797	08 Aug 18	Event Type C	12 Jul 18	03 Apr 18	16 Jul 18	#VALUE!	4
39	6106691797	08 Aug 18	Event Type C	12 Jul 18	03 Apr 18	16 Jul 18	#VALUE!	5
Sheet1

Cell Formulas
Range	Formula
E23:E39	E23	=AGGREGATE(14,6,[Event Date] /(([Name]=[@Name])* ([Event]="Event Type A")* ([Event Date]<=[@[Event Date]]) ),1)
F23:F39	F23	=AGGREGATE(15,6,[Event Date] /(([Name]=[@Name])* ([Event]=[@Event]) ),1)
G23:G39	G23	=IF([@Event]="Event Type A",[@[Event Date]],AGGREGATE(15,6,[Event Date] /(([Name]=[@Name])* ([Event]=[@Event])* ([Event Date]>=[@[Date of latest Type A so far]]) ),1))
H23:H39	H23	=IF([@Event]="Event Type A",1, AGGREGATE(2,6,[Event] /(([Name]=[@Name])* ([Event]=[@Event])* ([Event Date]>[@[Date of latest Type A so far]]) )))

Ref Post Number 10

I found a spare comma before <=

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

:

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

I think it should have been:

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

but actually although it's accepted now as a valid formula it's returning only "".

 

[jasonb75]

I've made a couple of adjustments to the formulas, I don't think that there is any problem with doing it the way that you have with columns E:G. I've seen others use the same but I prefer to use division all the way through, that way I know that there will be no erroneous zero results if I forget to enclose the whole array in an extra pair of parenthesis.

With reference to the #VALUE! errors, the rows that show a valid result are the ones where the result is shown without aggregate being calculated, the array method only works with specific function numbers, count is not one of them. Take a look at my replies in this thread for more info on this Use of AGGREGATE

Hopefully I haven't missed anything in the grab below.

Book1
	B	C	D	E	F	G	H	I
22	Name	Event Date	Event	Date of latest Type A so far	Date of first of this type of Event for this Name	Date of first of this type of Event for this Name since the last Type A	Running count of this type of event for this Name since the last Type A using AGGREGATE	Days since last Type A
23	6106691797	01 March 2018	Event Type A	01 March 2018	01 March 2018	01 March 2018	1
24	6106691797	01 March 2018	Event Type D	01 March 2018	01 March 2018	01 March 2018	1	0
25	6106691797	23 March 2018	Event Type A	23 March 2018	01 March 2018	23 March 2018	1
26	6106691797	23 March 2018	Event Type D	23 March 2018	01 March 2018	23 March 2018	1	0
27	6106691797	28 March 2018	Event Type A	28 March 2018	01 March 2018	28 March 2018	1
28	6106691797	28 March 2018	Event Type D	28 March 2018	01 March 2018	28 March 2018	1	0
29	6106691797	03 April 2018	Event Type C	28 March 2018	03 April 2018	03 April 2018	1	6
30	6106691797	04 April 2018	Event Type B	28 March 2018	04 April 2018	04 April 2018	1	7
31	6106691797	12 July 2018	Event Type A	12 July 2018	01 March 2018	12 July 2018	1
32	6106691797	12 July 2018	Event Type D	12 July 2018	01 March 2018	12 July 2018	1	0
33	6106691797	16 July 2018	Event Type C	12 July 2018	03 April 2018	16 July 2018	1	4
34	6106691797	18 July 2018	Event Type B	12 July 2018	04 April 2018	18 July 2018	1	6
35	6106691797	20 July 2018	Event Type C	12 July 2018	03 April 2018	16 July 2018	2	8
36	6106691797	25 July 2018	Event Type B	12 July 2018	04 April 2018	18 July 2018	2	13
37	6106691797	27 July 2018	Event Type C	12 July 2018	03 April 2018	16 July 2018	3	15
38	6106691797	08 August 2018	Event Type C	12 July 2018	03 April 2018	16 July 2018	5	27
39	6106691797	08 August 2018	Event Type C	12 July 2018	03 April 2018	16 July 2018	5	27
Sheet1

Cell Formulas
Range	Formula
E23:E39	E23	=AGGREGATE(14,6,[Event Date] /([Name]=[@Name])/([Event]="Event Type A")/([Event Date]<=[@[Event Date]]),1)
F23:F39	F23	=AGGREGATE(15,6,[Event Date] /([Name]=[@Name])/([Event]=[@Event]),1)
G23:G39	G23	=IF([@Event]="Event Type A",[@[Event Date]],AGGREGATE(15,6,[Event Date] /([Name]=[@Name])/([Event]=[@Event])/([Event Date]>=[@[Date of latest Type A so far]]),1))
H23:H39	H23	=COUNTIFS([Name],[@Name],[Event],[@Event],[Date of latest Type A so far],[@[Date of latest Type A so far]],[Event Date],"<="&[@[Event Date]])
I23:I39	I23	=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)),""))

 

[kcmuppet]

Well Jason, Thank you very much. I've been using Excel for >25 years and today, thanks to your extended help and patience, is the first time I ever used AGGREGATE
...and it keeps me from having to wrestle with Power Pivot & DAX for a little while longer!

 

[kcmuppet]

Just pondering the example data again in post 16 ... How would I adjust the formula for column H currently:

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

...to keep a running count even when two events happen on the same day? - i.e. so that the value in H38 would be 4, not 5?

 

[jasonb75]

oops, I missed that one when I compared the results. That needs to use a dynamic / expanding range which can get a bit messy with table names.
I'll have another look at it but it might not get chance to test and post the formula until late saturday.

 

[jasonb75]

That needs to use a dynamic / expanding range which can get a bit messy with table names.

I've added the formula below using table names and normal ranges so that you can see the difference. Note that the normal range formula will not work correctly in a table. It looks fine here but it doesn't expand correctly when you add new rows.

Book1
	B	C	D	E	F	G
22	Name	Event Date	Event	Date of latest Type A so far	Running count of this type of event for this Name since the last Type A	Without table refs
23	6106691797	01/03/2018	Event Type A	01/03/2018	1	1
24	6106691797	01/03/2018	Event Type D	01/03/2018	1	1
25	6106691797	23/03/2018	Event Type A	23/03/2018	1	1
26	6106691797	23/03/2018	Event Type D	23/03/2018	1	1
27	6106691797	28/03/2018	Event Type A	28/03/2018	1	1
28	6106691797	28/03/2018	Event Type D	28/03/2018	1	1
29	6106691797	03/04/2018	Event Type C	28/03/2018	1	1
30	6106691797	04/04/2018	Event Type B	28/03/2018	1	1
31	6106691797	12/07/2018	Event Type A	12/07/2018	1	1
32	6106691797	12/07/2018	Event Type D	12/07/2018	1	1
33	6106691797	16/07/2018	Event Type C	12/07/2018	1	1
34	6106691797	18/07/2018	Event Type B	12/07/2018	1	1
35	6106691797	20/07/2018	Event Type C	12/07/2018	2	2
36	6106691797	25/07/2018	Event Type B	12/07/2018	2	2
37	6106691797	27/07/2018	Event Type C	12/07/2018	3	3
38	6106691797	08/08/2018	Event Type C	12/07/2018	4	4
39	6106691797	08/08/2018	Event Type C	12/07/2018	5	5
Sheet1

Cell Formulas
Range	Formula
E23:E39	E23	=AGGREGATE(14,6,[Event Date] /([Name]=[@Name])/([Event]="Event Type A")/([Event Date]<=[@[Event Date]]),1)
F23:F39	F23	=COUNTIFS(Table1[[#Headers],[Name]]:INDEX([Name],ROW([@Name])-ROW(Table1[[#Headers],[Name]])),[@Name], Table1[[#Headers],[Event]]:INDEX([Event],ROW([@Event])-ROW(Table1[[#Headers],[Event]])),[@Event], Table1[[#Headers],[Date of latest Type A so far]]:INDEX([Date of latest Type A so far],ROW([@[Date of latest Type A so far]])-ROW(Table1[[#Headers],[Date of latest Type A so far]])),[@[Date of latest Type A so far]], Table1[[#Headers],[Event Date]]:INDEX([Event Date],ROW([@[Event Date]])-ROW(Table1[[#Headers],[Event Date]])),"<="&[@[Event Date]])
G23:G39	G23	=COUNTIFS(B$23:B23,B23,D$23:D23,D23,E$23:E23,E23,C$23:C23,"<="&C23)