Trimmean function

[jsical]

I have the following Data: Amount |Date | Spread for a large set of data. I know how to use TRIMMEAN but i am not sure how to incorporate multiple conditions. i.e I would like to use trimmean to looked at Amounts >=100 &<=500, and Dates >= 01/01/2021 &<=12/31/21, and return the mean Spread base on the data that falls between the 2 criteria of Amount and Date. i am not sure what the syntax would look like. I can do this for an specific set of data but not for in between two sets of data =TRIMMEAN(IF((Amount=50)*(date =12/01/31),Spread),10%) . any help would be greatly appreciated.

 

[jasonb75]

Welcome to MrExcel

It is not a function that I'm familair with, but it should work the same as any other with additional criteria, see if this format works for what you need.

=TRIMMEAN(IF((Amount>=100)*(Amount<=500)*(date >=start date)*(date <=end date),Spread),10%)

 

[jsical]

Thank you JasonB75, unfortunately the syntax doesn't work

 

[jasonb75]

How did you try entering the formula? I see no reason why it shouldn't work as long as your dates are all in valid formats.

 

[jsical]

Okay, Formats definitely were an issue. however, the formula still returning #num!.

 

[jasonb75]

That should only happen if there are no numeric 'spread' values meeting the criteria.

 

[jsical]

=TRIMMEAN(IF((Amount>=50)*(Amount<=300)*(Date>=1/1/21)*(Date<=12/31/22),spread),25%) this is the formula and data i using. you can see that there are number that meat the criterias. this is a small portion of the data, i have over 10k lines

Amount	Date	Spread
75​	9/1/2023​	59​
100​	6/1/2022​	52​
150​	3/1/2022​	9​
75​	2/1/2022​	29​
80​	9/1/2022​	115​
50​	3/1/2021​	31​
125​	11/15/2021​	21​
50​	10/1/2022​	48​
275​	2/1/2023​	22​
50​	7/1/2021​	5​
120​	2/1/2022​	19​
235​	7/15/2022​	40​
250​	8/1/2023​	23​
215​	3/1/2023​	4​
100​	7/1/2021​	38​
65​	7/1/2022​	87​
85​	8/15/2021​	35​
90​	9/1/2022​	60​
180​	12/1/2021​	16​
250​	11/1/2023​	50​
235​	7/1/2022​	67​
75​	9/1/2023​	22​
255​	11/1/2021​	30​
90​	7/1/2021​	50​
100​	9/1/2022​	46​
60​	10/1/2023​	47​
150​	9/1/2022​	8​
50​	8/15/2022​	36​
100​	2/15/2022​	32​
115​	7/1/2021​	36​
70​	7/1/2021​	58​
100​	8/1/2022​	49​
125​	8/15/2022​	27​
275​	7/15/2022​	38​
100​	12/1/2023​	34​
90​	7/1/2021​	45​
300​	10/15/2021​	27​
50​	11/15/2021​	53​
150​	7/1/2023​	41​
200​	8/15/2023​	41​
90​	10/1/2023​	22​
50​	10/1/2022​	48​
50​	10/1/2023​	41​
50​	2/1/2022​	44​
50​	10/1/2021​	29​

 

[jasonb75]

This is what I get with your sample and the same formula. Are you entering the dates in the same way?

Book1
	A	B	C	D	E
1	Amount	Date	Spread
2	75	09/01/23	59
3	100	06/01/22	52		01/01/21
4	150	03/01/22	9		12/31/22
5	75	02/01/22	29		38.44
6	80	09/01/22	115
7	50	03/01/21	31
8	125	11/15/21	21
9	50	10/01/22	48
10	275	02/01/23	22
11	50	07/01/21	5
12	120	02/01/22	19
13	235	07/15/22	40
14	250	08/01/23	23
15	215	03/01/23	4
16	100	07/01/21	38
17	65	07/01/22	87
18	85	08/15/21	35
19	90	09/01/22	60
20	180	12/01/21	16
21	250	11/01/23	50
22	235	07/01/22	67
23	75	09/01/23	22
24	255	11/01/21	30
25	90	07/01/21	50
26	100	09/01/22	46
27	60	10/01/23	47
28	150	09/01/22	8
29	50	08/15/22	36
30	100	02/15/22	32
31	115	07/01/21	36
32	70	07/01/21	58
33	100	08/01/22	49
34	125	08/15/22	27
35	275	07/15/22	38
36	100	12/01/23	34
37	90	07/01/21	45
38	300	10/15/21	27
39	50	11/15/21	53
40	150	07/01/23	41
41	200	08/15/23	41
42	90	10/01/23	22
43	50	10/01/22	48
44	50	10/01/23	41
45	50	02/01/22	44
46	50	10/01/21	29
Sheet4

Cell Formulas
Range	Formula
E5	E5	=TRIMMEAN(IF((A2:A46>=50)*(A2:A46<=300)*(B2:B46>=E3)*(B2:B46<=E4),C2:C46),25%)
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[jsical]

it might be cause i am not doing (Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces. ) let me try that

 

[jsical]

Thank you Thank you, that work perfectly.