Simplifying a formula

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

Hi,

Thanks for your interest in my problem.

Here is a sample file of the data. This one is for 10 participants.

I have no way of knowing how many contiguous fixations might appear within one AOI for one participant, so I wrote the formula to cover up to 20 contiguous fixations, which was more than double the size that I found, but I thought better safe than sorry.

Cheers

hi update the expected result manually .
if possible with explanation
Punnam