Results 1 to 13 of 13
Thread: Min,Max,AVG (MSO03)

20060205, 19:09 #1
 Join Date
 Dec 2001
 Location
 Chandler, AZ
 Posts
 2,160
 Thanks
 45
 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)

20060205, 20:02 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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)))))))

20060205, 20:35 #3
 Join Date
 Dec 2001
 Location
 Chandler, AZ
 Posts
 2,160
 Thanks
 45
 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)

20060205, 20:42 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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?

20060205, 20:57 #5
 Join Date
 Dec 2001
 Location
 Chandler, AZ
 Posts
 2,160
 Thanks
 45
 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)

20060205, 20:59 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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.

20060206, 11:32 #7
 Join Date
 Dec 2001
 Location
 Chandler, AZ
 Posts
 2,160
 Thanks
 45
 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 biweekly 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)

20060206, 11:36 #8
 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
ctrlshiftenter
Steve

20060206, 14:11 #9
 Join Date
 Dec 2001
 Location
 Chandler, AZ
 Posts
 2,160
 Thanks
 45
 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)

20060206, 14:35 #10
 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

20060206, 14:40 #11
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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?

20060206, 15:07 #12
 Join Date
 Dec 2001
 Location
 Chandler, AZ
 Posts
 2,160
 Thanks
 45
 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)

20060207, 20:10 #13
 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.
ChuckChuck Reimer
I'm from the Government and I'm here to help...