Forum Discussion
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)
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.
7 Replies
- formula enter in excel sheet
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!
- Thank You All.
Flattening file and pivoting worked for me 🙂 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.
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.
