+ Reply to Thread
Results 1 to 5 of 5

Changing range

  1. #1
    Registered User
    Join Date
    10-30-2018
    Location
    Ramat Gan, Israel
    MS-Off Ver
    Office 365
    Posts
    15

    Unhappy Changing range

    To all Excel specialist (as a profession or a hobby) good day,

    I measure pupil dilation and eye movement (which include for example fixation - a gaze on the same spot and saccades - a quick eye movement to the next spot. A fixation occurs between 2 saccades) and I began with data that include too many rows (multiple rows for each fixation - 13,000~ in general...a very shortened version is attached) which I wanted to base on for a pivot table.

    I managed to narrow it down to a single serial number for each fixation (column P) but it returns only the pupil size for the row of the fixation's serial number instead of the average pupil size for the whole rows referring to the same fixation.

    I tried to use a formula to get these averages but it only picks the average for X number of cells instead of the average for an entire fixation, which can take a varying number of rows.
    Ideally, I'd like Excel to "understand" that when P contains a number, I want the S (for the left pupil, or T for the right) column to contain the average for Q (for the left, or R for the right) until the next number in P. By coincidence for the first fixation it was Q2:Q14 but as I said, for the next fixations the number of cells may vary.

    Could you please help me?

    Thanks a lot,
    Rakefet
    Last edited by Rakel; 10-12-2019 at 09:52 AM. Reason: Problem solved

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    19,143

    Re: Changing range

    I couldn't open your file. I think it is because there are non-ASCII characters in the filename.

    Can you rename it on your PC and attach it again?

    Pete

  3. #3
    Registered User
    Join Date
    10-30-2018
    Location
    Ramat Gan, Israel
    MS-Off Ver
    Office 365
    Posts
    15

    Re: Changing range

    Thanks for your response.
    Sorry, a file with an ASCII name is attached now.

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    19,143

    Re: Changing range

    You can use this formula in S2:

    =IF(P2="","",AVERAGE(Q2:INDEX(Q:Q,MATCH(P2+1,P:P,0))))

    and a very similar formula in T2:

    =IF(P2="","",AVERAGE(R2:INDEX(R:R,MATCH(P2+1,P:P,0))))

    Copy these down as far as you need them. For the last AVERAGE to work properly, you will need to put the next serial number in the first blank cell at the bottom of column P (i.e. put 6 in P344 in this example).

    Hope this helps.

    Pete

    EDIT: You may need to use semicolons ( ; ) instead of commas ( , ) in the formulae, depending on your regional settings.
    Last edited by Pete_UK; 10-12-2019 at 06:56 AM.

  5. #5
    Registered User
    Join Date
    10-30-2018
    Location
    Ramat Gan, Israel
    MS-Off Ver
    Office 365
    Posts
    15

    Re: Changing range

    Hi Pete,

    It worked! Thanks a lot!

    Rakefet

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1