MIN per Category of Items

[gcefaloni]

Hi guys,

I have a question regarding how to calculate minimum cost (using MIN function) per category of items.
[TABLE="width: 680"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Fund Name & Series[/TD]
[TD]=LEFT(A2,20)[/TD]
[TD]COST (% of assets)[/TD]
[TD]=MIN COST PER FUND[/TD]
[/TR]
[TR]
[TD]AGF Canadian Money Market Fund MF Series[/TD]
[TD]AGF Canadian Money M[/TD]
[TD="align: right"]0.626851[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AGF Canadian Money Market Fund MF Series[/TD]
[TD]AGF Canadian Money M[/TD]
[TD="align: right"]0.626851[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AGF Canadian Money Market Fund MF Series[/TD]
[TD]AGF Canadian Money M[/TD]
[TD="align: right"]0.626851[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Invesco Canada Money Market Fund Ser A[/TD]
[TD]Invesco Canada Money[/TD]
[TD="align: right"]0.700001[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Invesco Canada Money Market Fund Ser A[/TD]
[TD]Invesco Canada Money[/TD]
[TD="align: right"]0.700001[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Invesco Canada Money Market Fund Ser A[/TD]
[TD]Invesco Canada Money[/TD]
[TD="align: right"]0.700001[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Invesco Canada Money Market Fund Ser A[/TD]
[TD]Invesco Canada Money[/TD]
[TD="align: right"]0.700001[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]National Bank Short Term Canadian Income Fund Inv[/TD]
[TD]National Bank Short [/TD]
[TD="align: right"]0.5042[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Barreau du Quebec Bond Fund[/TD]
[TD]Barreau du Quebec Bo[/TD]
[TD="align: right"]0.784[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Scotia T-Bill Fund - Series A[/TD]
[TD]Scotia T-Bill Fund -[/TD]
[TD="align: right"]0.48[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Scotia Canadian Bond Index Fund Series A[/TD]
[TD]Scotia Canadian Bond[/TD]
[TD="align: right"]0.85[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Scotia Money Market Fund Series A[/TD]
[TD]Scotia Money Market [/TD]
[TD="align: right"]0.76[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Scotia Premium T-Bill ($100M - $250M) Series A[/TD]
[TD]Scotia Premium T-Bil[/TD]
[TD="align: right"]0.46[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Scotia Premium T-Bill ($250M - $1MM) Series A[/TD]
[TD]Scotia Premium T-Bil[/TD]
[TD="align: right"]0.46[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Scotia Premium T-Bill $>$1MM Series A[/TD]
[TD]Scotia Premium T-Bil[/TD]
[TD="align: right"]0.46[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BMO Money Market Fund Series A[/TD]
[TD]BMO Money Market Fun[/TD]
[TD="align: right"]0.81[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BMO Money Market Fund Series A[/TD]
[TD]BMO Money Market Fun[/TD]
[TD="align: right"]0.81[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CI Money Market Fund Class A[/TD]
[TD]CI Money Market Fund[/TD]
[TD="align: right"]0.76[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CI Money Market Fund Class A[/TD]
[TD]CI Money Market Fund[/TD]
[TD="align: right"]0.76[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CI Money Market Fund Class A[/TD]
[TD]CI Money Market Fund[/TD]
[TD="align: right"]0.76[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CIBC Money Market Fund Class A[/TD]
[TD]CIBC Money Market Fu[/TD]
[TD="align: right"]0.65[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CIBC Canadian T-Bill Fund Premium Class[/TD]
[TD]CIBC Canadian T-Bill[/TD]
[TD="align: right"]0.38[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CI Short-Term Corporate Class A[/TD]
[TD]CI Short-Term Corpor[/TD]
[TD="align: right"]0.78[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CI Short-Term Corporate Class A[/TD]
[TD]CI Short-Term Corpor[/TD]
[TD="align: right"]0.78[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CI Short-Term Corporate Class A[/TD]
[TD]CI Short-Term Corpor[/TD]
[TD="align: right"]0.78[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CI Short-Term Corporate Class A[/TD]
[TD]CI Short-Term Corpor[/TD]
[TD="align: right"]0.78[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Desjardins Money Market Fund A Class[/TD]
[TD]Desjardins Money Mar[/TD]
[TD="align: right"]0.68[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Manulife Money Fund Advisor Series[/TD]
[TD]Manulife Money Fund [/TD]
[TD="align: right"]0.18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Manulife Money Fund Advisor Series[/TD]
[TD]Manulife Money Fund [/TD]
[TD="align: right"]0.18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Manulife Money Fund Advisor Series[/TD]
[TD]Manulife Money Fund [/TD]
[TD="align: right"]0.18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Manulife Money Fund Advisor Series[/TD]
[TD]Manulife Money Fund [/TD]
[TD="align: right"]0.18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Manulife Money Fund Advisor Series[/TD]
[TD]Manulife Money Fund [/TD]
[TD="align: right"]0.18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fidelity Canadian Money Market Fund Series A[/TD]
[TD]Fidelity Canadian Mo[/TD]
[TD="align: right"]0.64[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fidelity Canadian Money Market Fund Series A[/TD]
[TD]Fidelity Canadian Mo[/TD]
[TD="align: right"]0.64[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fidelity Canadian Money Market Fund Series A[/TD]
[TD]Fidelity Canadian Mo[/TD]
[TD="align: right"]0.64[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FMOQ Money Market Fund[/TD]
[TD]FMOQ Money Market Fu[/TD]
[TD="align: right"]0.4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FDP Cash Management Portfolio Series A[/TD]
[TD]FDP Cash Management [/TD]
[TD="align: right"]0.6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Beutel Goodman Income Fund Class D[/TD]
[TD]Beutel Goodman Incom[/TD]
[TD="align: right"]0.78[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Beutel Goodman Money Market Fund Class D[/TD]
[TD]Beutel Goodman Money[/TD]
[TD="align: right"]0.22[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]United Global Fixed Income Pool Class W[/TD]
[TD]United Global Fixed [/TD]
[TD="align: right"]0.2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]United Global Fixed Income Pool Class W[/TD]
[TD]United Global Fixed [/TD]
[TD="align: right"]0.2[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I'm trying to figure out how to return in Column D the lowest cost (Column C) per each fund (Column A) so that I could filter out all the more expensive funds and only have the data for the least expensive Class of each fund.

Thanks a lot in advance for the help, it will be very appreciated.

Gabriel

 

[Rada1]

What is your desired result in column D, can you give an example?

 

[Trouttrap2]

In your example the cost seems to be the same for each category. For example, AGF Canadian Money M = 0.626851 for each entry. If they weren't, then you could use {=MIN(IF(B2=$B$2:$B$42,$C$2:$C$42))}. You have to Ctrl + Shft+Enter the formula because its an array formula. Another idea is to use a Pivot Table and take the minimum of the Values area.

 

[Rada1]

Same costs got me confused too, maybe minimum for some group of items from column A is desired... but I doubt...

 

[gcefaloni]

In your example the cost seems to be the same for each category. For example, AGF Canadian Money M = 0.626851 for each entry. If they weren't, then you could use {=MIN(IF(B2=$B$2:$B$42,$C$2:$C$42))}. You have to Ctrl + Shft+Enter the formula because its an array formula. Another idea is to use a Pivot Table and take the minimum of the Values area.

Yea, sorry I copied a wrong portion of the data. It is a huge dataset of like 150,000 lines, some have series showing different costs per line some show the same.. But essentially if the first fund "AGF Canadian Money Market Fund MF Series" had one cost at 0.6281 and one at .33 and one at 0.21, I would want all 3 to show 0.21 for those 3 lines and then do the same for the next fund so that I have the lowest amount for each fund. Then I would be able to do another column that said if value in Column C = valuein Column D then show 1 else 0 and basically filter out all the 0s to have only the series that cost the less for each fund.

 

[FDibbins]

In your example the cost seems to be the same for each category. For example, AGF Canadian Money M = 0.626851 for each entry. If they weren't, then you could use {=MIN(IF(B2=$B$2:$B$42,$C$2:$C$42))}. You have to Ctrl + Shft+Enter the formula because its an array formula. Another idea is to use a Pivot Table and take the minimum of the Values area.

Just a suggestion - when posting an ARRAY formula, don't include the {}, if the user is new, they may copy that to their cell as well, and then try and CSE, which will not work, and give them an error message