Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Apr 2001
    Location
    SOUTH AFRICA
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formula query (97)

    Hi all,

    Could anyone please tell me how to do the following. I have a large spreadsheet where I want to deduct the kilo's in row a2 from row a1 and then row a4 from row a3 ect. In other words I want the formula copied to every 2nd row to enable me to work out an average.

    Thanks

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula query (97)

    This array formula seems to do it all in one cell:

    =AVERAGE(IF(MOD(ROW(A2:A100),2)>0,"",A2:A100-A1:A99))

    (press control-shift-enter when done editing the formula to tell XL it is an array formula)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Star Lounger
    Join Date
    Apr 2001
    Location
    SOUTH AFRICA
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula query (97)

    Baie dankie, Thank you very much for the reply. Is it possible to explain the formula so I can actually understand what I am doing.

    Thanks again

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula query (97)

    Explanation.

    =AVERAGE(IF(MOD(ROW(A2:A100),2)>0,"",A2:A100-A1:A99))

    Lets do this for just rows 2-5.

    ROW(A2:A5) yields this array:
    {2,3,4,5}

    MOD({2,3,4,5},2) yields:
    {0,1,0,1}

    Thus
    IF({0,1,0}>0,Trueclause, Falseclause)
    yields
    {Falseclause, Trueclause, Falseclause, Trueclause}

    So the end result:

    Average({"", A3-A2,"",A5-A4})

    Clear as mud?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Star Lounger
    Join Date
    Apr 2001
    Location
    SOUTH AFRICA
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula query (97)

    Excellent stuff, thank you Jan Karel

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula query (97)

    I assume you mean cell A1, A2 etc.

    In cell B2 put the formula =A1-A2.
    Select cells B1and B2.
    Put the cursor in the bottom right corner of B2 to bring up the fill handle, then double click it.

    In Col B you now have alternating blanks and formulas.

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula query (97)

    Hey, I liked my complicated solution better ! <vbg>
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    Star Lounger
    Join Date
    Apr 2001
    Location
    SOUTH AFRICA
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula query (97)

    Thanks to both of you. It's nice to learn something new and usefull from you gurus.

Posting Permissions

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