Results 1 to 13 of 13
  1. #1
    Silver Lounger t8ntlikly's Avatar
    Join Date
    Dec 2001
    Location
    Chandler, AZ
    Posts
    2,162
    Thanks
    46
    Thanked 13 Times in 11 Posts

    Min,Max,AVG (MSO03)

    I have a column of 5 numbers, to get the lowest number of those I have the formula =Min(Ax:Ax), but I will be adding numbers to this column, When I get to 6,7, and 8 numbers I need to find the lowest two numbers and average those. When I get to 9 - 19 numbers I need to find the lowest three, and average those. When I get to 20 numbers and over I need to use the most recent of the 20 numbers and average the lowest 10.
    Thanks John
    Teamwork is essential; it gives the enemy other people to shoot at. (Murphy's War Laws #39)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Min,Max,AVG (MSO03)

    Assuming the numbers are in A1:An, try the following array formula (confirm with Ctrl+Alt+Enter): <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

    =AVERAGE(SMALL(INDIRECT("A"&MAX(COUNTA(A:A)-19,1)&":A"&COUNTA(A:A)),ROW(INDIRECT("1:"&IF(COUNT A(A:A)<=5,1,IF(COUNTA(A:A)<=8,2,IF(COUNTA(A:A)<=19 ,3,10)))))))

  3. #3
    Silver Lounger t8ntlikly's Avatar
    Join Date
    Dec 2001
    Location
    Chandler, AZ
    Posts
    2,162
    Thanks
    46
    Thanked 13 Times in 11 Posts

    Re: Min,Max,AVG (MSO03)

    Couldn't get that to work Hans. I have attached a spreadsheet with some numbers on it, showing what I am trying to get.
    Thanks John
    Teamwork is essential; it gives the enemy other people to shoot at. (Murphy's War Laws #39)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Min,Max,AVG (MSO03)

    In your first post, you wrote "When I get to 20 numbers and over I need to use the most recent of the 20 numbers and average the lowest 10". Now you apparently want to average the lowest 3 of the last 20. Which is it to be?

  5. #5
    Silver Lounger t8ntlikly's Avatar
    Join Date
    Dec 2001
    Location
    Chandler, AZ
    Posts
    2,162
    Thanks
    46
    Thanked 13 Times in 11 Posts

    Re: Min,Max,AVG (MSO03)

    I apologize Hans, I want to use the most recent 20 and average the lowest 10 of those.
    Thanks John
    Teamwork is essential; it gives the enemy other people to shoot at. (Murphy's War Laws #39)

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Min,Max,AVG (MSO03)

    Then my formula should do what you want, PROVIDED you enter it as an array formula. You entered it as a standard formula.

  7. #7
    Silver Lounger t8ntlikly's Avatar
    Join Date
    Dec 2001
    Location
    Chandler, AZ
    Posts
    2,162
    Thanks
    46
    Thanked 13 Times in 11 Posts

    Re: Min,Max,AVG (MSO03)

    OK, I am having a heck of a time trying to figure this out. I have entered the formula as you have written it, and then clicked (confirmed)CTRL+ALT+ENTER, and nothing changes, it doesn't show it as an array, with the {} around the formula as indicated in the "help" files. Im really trying to learn this, but I must be missing something.I also noticed that because I hadn't written any numbers in A1 if got a "ref" error, and when this is first opened there won't be any numbers in that column. Im sorry Hans I left that out. The numbers will be ebtered on a weekly or bi-weekly basis.
    Sorry to sound like a dunce here, but as my wise ole pappy once said "The only stupid question is the one not asked"
    Thanks John
    Teamwork is essential; it gives the enemy other people to shoot at. (Murphy's War Laws #39)

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Min,Max,AVG (MSO03)

    select the cell
    press <F2> to get in edit mode
    ctrl-shift-enter

    Steve

  9. #9
    Silver Lounger t8ntlikly's Avatar
    Join Date
    Dec 2001
    Location
    Chandler, AZ
    Posts
    2,162
    Thanks
    46
    Thanked 13 Times in 11 Posts

    Re: Min,Max,AVG (MSO03)

    Talk about feeling like a complete blockhead! I attached the worksheet. Sorry guys this just inst sinking in for some reason
    Thanks John
    Teamwork is essential; it gives the enemy other people to shoot at. (Murphy's War Laws #39)

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Min,Max,AVG (MSO03)

    It works. You have 20 numbers, the lowest 10 are all zero so the avg of 10 zeroes is zero....

    Steve

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Min,Max,AVG (MSO03)

    How difficult can it be to
    1) Select W7
    2) Press F2 to edit the cell
    3) Press Ctrl+Shift+Enter to make the formula into an array formula
    <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

    You stated that the column could contain positive, negative and zero entries, but from the workbook you attached I'd guess you don't want to include the sums of the empty rows in the average. If that is correct, you should change the sum formulas to return "" for a blank row.

    Moreover, your data do not start in row 1, but in row 7, so you have to adjust for that.
    Does the attached version do what you want?

  12. #12
    Silver Lounger t8ntlikly's Avatar
    Join Date
    Dec 2001
    Location
    Chandler, AZ
    Posts
    2,162
    Thanks
    46
    Thanked 13 Times in 11 Posts

    Re: Min,Max,AVG (MSO03)

    Yes Hans it does. In looking at the formula, I can follow it enough to see what you are doing. My problem was that when I did click F2, then ctrl+alt+enter, I wasnt getting the brackets like you now show. Hence the thing wasnt working.
    Thanks John
    Teamwork is essential; it gives the enemy other people to shoot at. (Murphy's War Laws #39)

  13. #13
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Min,Max,AVG (MSO03)

    John,

    Hans indicated pressing Ctrl + Shift + Enter and you
    indicated you were pressing Ctrl + ALT + Enter and it was not working.

    I am not sure if you saw the difference or not.

    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

Posting Permissions

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