SOLVED

New Contributor

# Excel Formula Problem

Hi, I have an excel sheet with thousands of rows four individual persons and hundreds of columns of categories. Each four consecutive columns can be clubbed as category. I want to do an analysis of this excel sheet and come up with an output as mentioned below in the sample.(Excel attached)

7 Replies

# Re: Excel Formula Problem

Not sure I would do this with thousands of rows & hundreds of columns (I would rather look at restructuring the sheet to enable this analysis and potentially other ones) but see attached file

best response confirmed by Haris1234 (New Contributor)
Solution

# Re: Excel Formula Problem

For best analysis change your structure from crosstabular form into a flat table:

Person - Category - Sub - Type - Value

Then it is just a matter of a quick pivot table.

# Re: Excel Formula Problem

@Haris1234 Since you mention "thousands of rows and hundreds of columns" I'd suggest you take a look at Power Query to flatten your data, as suggested by @Detlef Lewin .

From there, you can create the desired output within Power Query, in Power Pivot (Data Model) or in a regular pivot table.

All of these are demonstrated in the attached file.

# Re: Excel Formula Problem

Another Power Query + Power Pivot option (attached) in case you would need to analyse on others than A

# Re: Excel Formula Problem

Thank You All.
Flattening file and pivoting worked for me

# Re: Excel Formula Problem

formula enter in excel sheet

# Re: Excel Formula Problem

 Species name Frequency % Density Relative frequency (RF) Relative density(RD) IVI=RF+RD ni/N Paspalum sp 85 5.4 13.2192846 #DIV/0! Cynodon dactylon 65 10.9 10.1088647 #DIV/0! Dracocephalum moldavica 78 3.9 12.13063764 #DIV/0! Centella asiatica 40 3.15 6.220839813 #DIV/0! Alternanthera sp 50 2.45 #DIV/0! #DIV/0! cyperus sp 50 2.85 #DIV/0! #DIV/0! Eclipta sp 5 0.45 #DIV/0! #DIV/0! Spilanthes sp 35 1.05 #DIV/0! #DIV/0!