SOLVED

How to analyze excel data

%3CLINGO-SUB%20id%3D%22lingo-sub-3376410%22%20slang%3D%22en-US%22%3EHow%20to%20analyze%20excel%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3376410%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Excel_data.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F371717i8EEF4EF21B51FADE%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Excel_data.png%22%20alt%3D%22Excel_data.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20above%20attached%20data%20in%20excel%20sheet%20is%20a%20snap%20from%20my%20experiment.%20There%20are%20four%20columns%20with%20header%20.%20There%20are%20total%20280%20trials%20in%20total%20(40%20times%20each%20of%2050%2C100%2C150%2C200%2C250%2C300%2C350%20millisecond%26nbsp%3B%20stimulus%20duration).%20I%20want%20to%20see%20for%2050%20ms%20duration%20(which%20is%20total%2040%20in%20number)%2C%20how%20many%20number%20of%20'1'%20response%20button%20was%20pressed%20and%20how%20many%20number%20of%20'2'%20response%20button%20was%20pressed%3F%20Similarly%20for%20all%20other%20duration%20(100%2C150%2C200%2C250%2C300%2Cand%20350%20ms)%2C%20I%26nbsp%3B%20want%20to%20analyze%20the%20same%20thing.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help%20me%20out%20with%20this%20problem.%20I%20am%20not%20expert%20in%20Excel.%20Thanks%20in%20Advance.%20If%20you%20want%20me%20to%20upload%20or%20share%20excel%20sheet%20with%20you%2C%20I%20will%20share%20with%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3376410%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3376429%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20analyze%20excel%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3376429%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1390626%22%20target%3D%22_blank%22%3E%40Ankit27%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DCOUNTIFS(%24C%242%3A%24C%24281%2C%24F2%2C%24D%242%3A%24D%24281%2CG%241)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EYou%20can%20try%20the%20countifs%20formula.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3376433%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20analyze%20excel%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3376433%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1390626%22%20target%3D%22_blank%22%3E%40Ankit27%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DCOUNTIFS(%24C%242%3A%24C%24281%2C%24F2%2C%24D%242%3A%24D%24281%2CG%241)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EMaybe%20with%20the%20countifs%20formula.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CDIV%20class%3D%22%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22%22%3E%3CDIV%20class%3D%22%22%3E%3CDIV%20class%3D%22%22%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3376479%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20analyze%20excel%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3376479%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1390626%22%20target%3D%22_blank%22%3E%40Ankit27%3C%2FA%3E%26nbsp%3BAs%20a%20variant%2C%20consider%20using%20a%20pivot%20table.%3C%2FP%3E%3CP%3ESee%20attached.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3376507%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20analyze%20excel%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3376507%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1390626%22%20target%3D%22_blank%22%3E%40Ankit27%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOr%20the%20same%20formula%20'simplified'%20through%20the%20use%20of%20defined%20names%20and%20dynamic%20arrays%20(as%20opposed%20to%20direct%20references%20using%20mixed%20absolute%2Frelative%20notation%20and%20copy%2Ffills.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20747px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F371722iFD3E7A4FF28689AB%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3376528%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20analyze%20excel%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3376528%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1390626%22%20target%3D%22_blank%22%3E%40Ankit27%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DSUMPRODUCT((%24C%242%3A%24C%24281%3D%24F2)*(%24D%242%3A%24D%24281%3DG%241))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EAlternatives%20could%20be%20SUMPRODUCT%20or%20Power%20Query.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3377218%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20analyze%20excel%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3377218%22%20slang%3D%22en-US%22%3EThis%20is%20great%20.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3384933%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20analyze%20excel%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3384933%22%20slang%3D%22en-US%22%3EThanks%20a%20lot%20Quaruple.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3384936%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20analyze%20excel%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3384936%22%20slang%3D%22en-US%22%3EThanks%20a%20lot%20Peter%3C%2FLINGO-BODY%3E
Occasional Contributor

Excel_data.png

 

 

The above attached data in excel sheet is a snap from my experiment. There are four columns with header . There are total 280 trials in total (40 times each of 50,100,150,200,250,300,350 millisecond  stimulus duration). I want to see for 50 ms duration (which is total 40 in number), how many number of '1' response button was pressed and how many number of '2' response button was pressed? Similarly for all other duration (100,150,200,250,300,and 350 ms), I  want to analyze the same thing.

 

Please help me out with this problem. I am not expert in Excel. Thanks in Advance. If you want me to upload or share excel sheet with you, I will share with you.

7 Replies

@Ankit27 

 

=COUNTIFS($C$2:$C$281,$F2,$D$2:$D$281,G$1)

 

Maybe with the countifs formula. 

 
 
best response confirmed by Ankit27 (Occasional Contributor)
Solution

@Ankit27 As a variant, consider using a pivot table.

See attached.

 

@Ankit27 

Or the same formula 'simplified' through the use of defined names and dynamic arrays (as opposed to direct references using mixed absolute/relative notation and copy/fills.

 

image.png

@Ankit27 

=SUMPRODUCT(($C$2:$C$281=$F2)*($D$2:$D$281=G$1))

Alternatives could be SUMPRODUCT or Power Query. 

This is great .
Thanks a lot Quaruple.
Thanks a lot Peter