Results 1 to 3 of 3

Thread: excel incrementing answers

  1. #1

    Question excel incrementing answers



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Good day.

    I need help with the following please.

    I have a lot of values in column j,k,l . like for instance.
    Column.......J .......K .......L .........M.........................N.....................O
    .................1 ...... 5 ......4
    .................8 ...... 7 ......3
    .................2....... 7.......8
    ............................................................................
    row 17.......2.........5......7
    row 18.......2.........5......7.......=min(J1:j20)...=min(K1:k20)...=min(L1:l20)
    row 19.......2.........5......7.......=max(J1:j20)...=max(K1:k20)...=max(L1:l20)
    row 20.......2.........5......7.......=AVE(J1:j20)...=AVE(K1:k20).....=AVE(L1:l20)
    row 21.......2.........5......7 .............................======> #1 of new set of 20 values ( at row 40 new Average)
    row 22

    (etc)
    this goes on forever.

    Now my problem is. I need to work out the Min Max and average value every 20'th data value in column J ,K,L, and I need the answer to appear in J-M K-N L-O underneath
    I wrote an =if equation but my data for min and max are skewed when I fill it through the rest of my data. !!!

    I hope you understand.

    Please help I will send you a picture of my data if needed.

    Thank you
    Arnoux

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    If I understood correctly, try this formula in M1:

    =IF(MOD(ROWS(J$1:J1),20)=18,MIN(OFFSET(J1,-17,,20,1)),IF(MOD(ROWS(J$1:J1),20)=19,MAX(OFFSET(J1,-18,,20,1)),IF(MOD(ROWS(J$1:J1),20)=0,AVERAGE(OFFSET(J1,-19,,20,1)),"")))

    copied across to O1 and then down the rows to the bottom of the dataset. Every 18th to 20th row will have results.


  3. #3

    Cool

    Hi NBVC. Thanks for the help.The function works perfectly. You are an absolute genius. Well done

Posting Permissions

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