1. 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.

2. 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. 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.

4. 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. Re: Min,Max,AVG (MSO03)

I apologize Hans, I want to use the most recent 20 and average the lowest 10 of those.

6. 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. 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"

8. Re: Min,Max,AVG (MSO03)

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

Steve

9. 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

10. 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. 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. 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.

13. 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

Posting Permissions

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