Multiple Percent based on MATCH feature

[nostradamus]

Need Formula for cell E2, cell F2, and cell F3
E2 should reflect the Order (in col A) that occurs the most number of times. In case there are two Orders that are highest, then take the one with highest abundance total (in col D).
F2 should reflect the percentage of that particular order in col E2 (the formula should be independent of value being in col E2 or not).
F3 - this one is similar to F2, but I want the percentage of the top three Orders (from col A)




****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 11">****** name="Originator" content="Microsoft Word 11">

	A	B	C	D	E	F
1	Order	Family	Genus	Abundance	Dominant Taxa	Percentage
2	Ephemeroptera	Baetidae	Baetis	22	Ephemeroptera
3	Ephemeroptera	Baetidae	Dactylobaetis	102
4	Coleoptera	Elmidae	Ancyronyx	21
5	Trichoptera	Hydrobiosidae	Atopsyche	13
6	Ephemeroptera	Baetidae	Paracloeodes	44
7	Ephemeroptera	Baetidae	Pseudocloeon	65
8
9

<tbody>
</tbody>

[/QUOTE]

 

[Eric W]

Maybe something like this?

	A	B	C	D	E	F	G
1	Order	Family	Genus	Abundance	Dominant Taxa	Percentage
2	Ephemeroptera	Baetidae	Baetis	22	Ephemeroptera	87.27%
3	Ephemeroptera	Baetidae	Dactylobaetis	102	Coleoptera	7.87%
4	Coleoptera	Elmidae	Ancyronyx	21	Trichoptera	4.87%
5	Trichoptera	Hydrobiosidae	Atopsyche	13
6	Ephemeroptera	Baetidae	Paracloeodes	44
7	Ephemeroptera	Baetidae	Pseudocloeon	65

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet5​

Worksheet Formulas Cell Formula F2 =SUMIF($A$2:$A$7,E2,$D$2:$D$7)/SUM(D:D) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula E2 {=INDEX($A$2:$A$7,MATCH(MAX(IF(COUNTIF($E$1:$E1,$A$2:$A$7)=0,COUNTIF($A$2:$A$7,$A$2:$A$7)*100000+SUMIF($A$2:$A$7,$A$2:$A$7,$D$2:$D$7))),IF(COUNTIF($E$1:$E1,$A$2:$A$7)=0,COUNTIF($A$2:$A$7,$A$2:$A$7)*100000+SUMIF($A$2:$A$7,$A$2:$A$7,$D$2:$D$7)),0))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Percentage is based on abundance. And I'm not sure if your F3 formula is supposed to be the sum of F2:F4, but it's easier to do this way.

 

[nostradamus]

Thanks Eric, your formula works great. But I have the formula columns in a different spreadsheet (same file), and when I plug in the formula for cells E2, E3, and E4, I am getting a weird output. Cell E2 is blank, value for E2 is showing up in E3, and value for E3 is showing up in E4. (I am accounting for the spreadsheet name to be put before the proper references as below: first line is your formula, and below is how I have substituted it in my spreadsheet)

your suggestion

{=INDEX($A$2:$A$7,MATCH(MAX(IF(COUNTIF($E$1:$E1,$A$2:$A$7)=0,COUNTIF($A$2:$A$7,$A$2:$A$7)*100000+SUMIF($A$2:$A$7,$A$2:$A$7,$D$2:$D$7))),IF(COUNTIF($E$1:$E1,$A$2:$A$7)=0,COUNTIF($A$2:$A$7,$A$2:$A$7)*100000+SUMIF($A$2:$A$7,$A$2:$A$7,$D$2:$D$7)),0))}

my substitution

{=INDEX(Data!$B$2:$B$1000,MATCH(MAX(IF(COUNTIF($B$15:$B15,Data!$B$2:$B$1000)=0,COUNTIF(Data!$B$2:$B$1000,Data!$B$2:$B$1000)*100000+SUMIF(Data!$B$2:$B$1000,Data!$B$2:$B$1000,Data!$F$2:$F$1000))),IF(COUNTIF($B$15:$B15,Data!$B$2:$B$1000)=0,COUNTIF(Data!$B$2:$B$1000,Data!$B$2:$B$1000)*100000+SUMIF(Data!$B$2:$B$1000,Data!$B$2:$B$1000,Data!$F$2:$F$1000)),0))}

I'm not sure if this is because I am trying to get the formulas on a different spreadsheet. If you could check into it, it would be awesome.

 

[Eric W]

Moving the formula to another sheet is a matter of adjusting the ranges, and making sure that the ranges pointing to another sheet have the sheet name, and ranges pointing to the current sheet don't have a sheet name (or have the current sheet name). I'm a little confused as to how your sheet looks. When I try to decipher your formula, it appears you've moved some of the ranges around, and I don't know where they actually are now. Nevertheless, if your Data sheet looks like this:

	A	B	C	D	E
1		Order	Family	Genus	Abundance
2		Ephemeroptera	Baetidae	Baetis	22
3		Ephemeroptera	Baetidae	Dactylobaetis	102
4		Coleoptera	Elmidae	Ancyronyx	21
5		Trichoptera	Hydrobiosidae	Atopsyche	13
6		Ephemeroptera	Baetidae	Paracloeodes	44
7		Ephemeroptera	Baetidae	Pseudocloeon	65

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Data​

Then you can put the formulas on another sheet like this:

	E	F
1	Dominant Taxa	Percentage
2	Ephemeroptera	87.27%
3	Coleoptera	7.87%
4	Trichoptera	4.87%

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula F2 =SUMIF(Data!$B$2:$B$1000,E2,Data!$E$2:$E$1000)/SUM(Data!E:E) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula E2 {=INDEX(Data!$B$2:$B$1000,MATCH(MAX(IF(COUNTIF($E$1:$E1,Data!$B$2:$B$1000)=0,COUNTIF(Data!$B$2:$B$1000,Data!$B$2:$B$1000)*100000+SUMIF(Data!$B$2:$B$1000,Data!$B$2:$B$1000,Data!$E$2:$E$1000))),IF(COUNTIF($E$1:$E1,Data!$B$2:$B$1000)=0,COUNTIF(Data!$B$2:$B$1000,Data!$B$2:$B$1000)*100000+SUMIF(Data!$B$2:$B$1000,Data!$B$2:$B$1000,Data!$E$2:$E$1000)),0))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Adjust the ranges as needed, just be sure they point to the right columns/sheet. Hope this helps.