Hi All,

I am helping out on a research project and have been given data cleaning and analysis to do. I am working with Excel formulas for the first time and have A LOT to learn... I am hoping that this forum will be helpful. I have to do quite a few data transformations to get the variables out of raw data, so I will probably be returning to this site for help regularly.

I will try to explain what I am doing as simply as possible, sorry if it is a bit long. The data that I am dealing with comes out ordered by participant and shows fixations on Areas of Interest (AOIs). Fixations are shown in milliseconds, with 0s where there are no fixations. There can be several consecutive fixations in one AOI within a single participant. I needed to sum all the consecutive fixations (with no 0s in between) within each participant, with the first fixation coming back with the sum of all the consecutive fixations below and all the later consecutive fixations coming back as 0s. The participant number is in column A and the first fixation variable is in column E. I found a way to use IF, AND and OR to write a formula describing (to 20 places) if there is a consecutive string of fixations within a participant, but it is REALLY long. I am sure that there is a simpler way of writing this, I am hoping that someone can help so that I can learn some better and less time consuming techniques. If I need to include any more information please let me know.

The formulas is as follows:

=IF(E2=0,0,IF(AND($A2=$A1,E1>0),0,IF(AND($A3=$A2,E3>0,OR($A3<>$A4,E4=0)),SUM(E2:E3),IF(AND($A3=$A2,$A4=$A2,E3>0,E4>0,OR($A4<>$A5,E5=0)),SUM(E2:E4),IF(AND($A3=$A2,$A4=$A2,$A5=$A2,E3>0,E4>0,E5>0,OR($A5<>$A6,E6=0)),SUM(E2:E5),IF(AND($A3=$A2,$A4=$A2,$A5=$A2,$A6=$A2,E3>0,E4>0,E5>0,E6>0,OR($A6<>$A7,E7=0)),SUM(E2:E6),IF(AND($A3=$A2,$A4=$A2,$A5=$A2,$A6=$A2,$A7=$A2,E3>0,E4>0,E5>0,E6>0,E7>0,OR($A7<>$A8,E8=0)),SUM(E2:E7),IF(AND($A3=$A2,$A4=$A2,$A5=$A2,$A6=$A2,$A7=$A2,$A8=$A2,E3>0,E4>0,E5>0,E6>0,E7>0,E8>0,OR($A8<>$A9,E9=0)),SUM(E2:E8),IF(AND($A3=$A2,$A4=$A2,$A5=$A2,$A6=$A2,$A7=$A2,$A8=$A2,$A9=$A2,E3>0,E4>0,E5>0,E6>0,E7>0,E8>0,E9>0,OR($A9<>$A10,E10=0)),SUM(E2:E9),IF(AND($A3=$A2,$A4=$A2,$A5=$A2,$A6=$A2,$A7=$A2,$A8=$A2,$A9=$A2,$A10=$A2,E3>0,E4>0,E5>0,E6>0,E7>0,E8>0,E9>0,E10>0,OR($A10<>$A11,E11=0)),SUM(E2:E10),IF(AND($A3=$A2,$A4=$A2,$A5=$A2,$A6=$A2,$A7=$A2,$A8=$A2,$A9=$A2,$A10=$A2,$A11=$A2,E3>0,E4>0,E5>0,E6>0,E7>0,E8>0,E9>0,E10>0,E11>0,OR($A11<>$A12,E12=0)),SUM(E2:E11),IF(AND($A3=$A2,$A4=$A2,$A5=$A2,$A6=$A2,$A7=$A2,$A8=$A2,$A9=$A2,$A10=$A2,$A11=$A2,$A12=$A2,E3>0,E4>0,E5>0,E6>0,E7>0,E8>0,E9>0,E10>0,E11>0,E12>0,OR($A12<>$A13,E13=0)),SUM(E2:E12),IF(AND($A3=$A2,$A4=$A2,$A5=$A2,$A6=$A2,$A7=$A2,$A8=$A2,$A9=$A2,$A10=$A2,$A11=$A2,$A12=$A2,$A13=$A2,E3>0,E4>0,E5>0,E6>0,E7>0,E8>0,E9>0,E10>0,E11>0,E12>0,E13>0,OR($A13<>$A14,E14=0)),SUM(E2:E13),IF(AND($A3=$A2,$A4=$A2,$A5=$A2,$A6=$A2,$A7=$A2,$A8=$A2,$A9=$A2,$A10=$A2,$A11=$A2,$A12=$A2,$A13=$A2,$A14=$A2,E3>0,E4>0,E5>0,E6>0,E7>0,E8>0,E9>0,E10>0,E11>0,E12>0,E13>0,E14>0,OR($A14<>$A15,E15=0)),SUM(E2:E14),IF(AND($A3=$A2,$A4=$A2,$A5=$A2,$A6=$A2,$A7=$A2,$A8=$A2,$A9=$A2,$A10=$A2,$A11=$A2,$A12=$A2,$A13=$A2,$A14=$A2,$A15=$A2,E3>0,E4>0,E5>0,E6>0,E7>0,E8>0,E9>0,E10>0,E11>0,E12>0,E13>0,E14>0,E15>0,OR($A15<>$A16,E16=0)),SUM(E2:E15),IF(AND($A3=$A2,$A4=$A2,$A5=$A2,$A6=$A2,$A7=$A2,$A8=$A2,$A9=$A2,$A10=$A2,$A11=$A2,$A12=$A2,$A13=$A2,$A14=$A2,$A15=$A2,$A16=$A2,E3>0,E4>0,E5>0,E6>0,E7>0,E8>0,E9>0,E10>0,E11>0,E12>0,E13>0,E14>0,E15>0,E16>0,OR($A16<>$A17,E17=0)),SUM(E2:E16),IF(AND($A3=$A2,$A4=$A2,$A5=$A2,$A6=$A2,$A7=$A2,$A8=$A2,$A9=$A2,$A10=$A2,$A11=$A2,$A12=$A2,$A13=$A2,$A14=$A2,$A15=$A2,$A16=$A2,$A17=$A2,E3>0,E4>0,E5>0,E6>0,E7>0,E8>0,E9>0,E10>0,E11>0,E12>0,E13>0,E14>0,E15>0,E16>0,E17>0,OR($A17<>$A18,E18=0)),SUM(E2:E17),IF(AND($A3=$A2,$A4=$A2,$A5=$A2,$A6=$A2,$A7=$A2,$A8=$A2,$A9=$A2,$A10=$A2,$A11=$A2,$A12=$A2,$A13=$A2,$A14=$A2,$A15=$A2,$A16=$A2,$A17=$A2,$A18=$A2,E3>0,E4>0,E5>0,E6>0,E7>0,E8>0,E9>0,E10>0,E11>0,E12>0,E13>0,E14>0,E15>0,E16>0,E17>0,E18>0,OR($A18<>$A19,E19=0)),SUM(E2:E18),IF(AND($A3=$A2,$A4=$A2,$A5=$A2,$A6=$A2,$A7=$A2,$A8=$A2,$A9=$A2,$A10=$A2,$A11=$A2,$A12=$A2,$A13=$A2,$A14=$A2,$A15=$A2,$A16=$A2,$A17=$A2,$A18=$A2,$A19=$A2,E3>0,E4>0,E5>0,E6>0,E7>0,E8>0,E9>0,E10>0,E11>0,E12>0,E13>0,E14>0,E15>0,E16>0,E17>0,E18>0,E19>0,OR($A19<>$A20,E20=0)),SUM(E2:E19),IF(AND($A3=$A2,$A4=$A2,$A5=$A2,$A6=$A2,$A7=$A2,$A8=$A2,$A9=$A2,$A10=$A2,$A11=$A2,$A12=$A2,$A13=$A2,$A14=$A2,$A15=$A2,$A16=$A2,$A17=$A2,$A18=$A2,$A19=$A2,$A20=$A2,E3>0,E4>0,E5>0,E6>0,E7>0,E8>0,E9>0,E10>0,E11>0,E12>0,E13>0,E14>0,E15>0,E16>0,E17>0,E18>0,E19>0,E20>0,OR($A20<>$A21,E21=0)),SUM(E2:E20),IF(AND($A3=$A2,$A4=$A2,$A5=$A2,$A6=$A2,$A7=$A2,$A8=$A2,$A9=$A2,$A10=$A2,$A11=$A2,$A12=$A2,$A13=$A2,$A14=$A2,$A15=$A2,$A16=$A2,$A17=$A2,$A18=$A2,$A19=$A2,$A20=$A2,$A21=$A2,E3>0,E4>0,E5>0,E6>0,E7>0,E8>0,E9>0,E10>0,E11>0,E12>0,E13>0,E14>0,E15>0,E16>0,E17>0,E18>0,E19>0,E20>0,E21>0,OR($A21<>$A22,E22=0)),SUM(E2:E21),IF(AND($A3=$A2,$A4=$A2,$A5=$A2,$A6=$A2,$A7=$A2,$A8=$A2,$A9=$A2,$A10=$A2,$A11=$A2,$A12=$A2,$A13=$A2,$A14=$A2,$A15=$A2,$A16=$A2,$A17=$A2,$A18=$A2,$A19=$A2,$A20=$A2,$A21=$A2,$A22=$A2,E3>0,E4>0,E5>0,E6>0,E7>0,E8>0,E9>0,E10>0,E11>0,E12>0,E13>0,E14>0,E15>0,E16>0,E17>0,E18>0,E19>0,E20>0,E21>0,E22>0),SUM(E2:E22),E2))))))))))))))))))))))

Thanks for your time