Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula - shorter, neater, faster ?
I'm trying to calculate some targets for next year based on this year's
achievements. So, assume I have twelve numbers in a row for January to December. I want to discard the three lowest scores and the three highest scores and then sum the remaining six values and divide by 6 to get an average. I have come up with the following formula but I'm sure there must be a shorter, neater and possibly faster way to write it (well, certainly faster to write with less possibility of getting it wrong !) =(SUM(A15:L15)-(SMALL(A15:L15,1)+SMALL(A15:L15,2)+SMALL(A15:L15,3 )+LARGE(A15 :L15,1)+LARGE(A15:L15,2)+LARGE(A15:L15,3)))/6 Any thoughts ? Of course I could just discard the TWO smallest and TWO largest ... which might be an option. Thanks for your help Trevor |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula - shorter, neater, faster ?
Trevor,
How about =(SUM(A15:L15)-(SUM(SMALL(A15:L15,{1,2,3}))+SUM(LARGE(A15:L15,{1, 2,3}))))/6 -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Trevor Shuttleworth" wrote in message ... I'm trying to calculate some targets for next year based on this year's achievements. So, assume I have twelve numbers in a row for January to December. I want to discard the three lowest scores and the three highest scores and then sum the remaining six values and divide by 6 to get an average. I have come up with the following formula but I'm sure there must be a shorter, neater and possibly faster way to write it (well, certainly faster to write with less possibility of getting it wrong !) =(SUM(A15:L15)-(SMALL(A15:L15,1)+SMALL(A15:L15,2)+SMALL(A15:L15,3 )+LARGE(A15 :L15,1)+LARGE(A15:L15,2)+LARGE(A15:L15,3)))/6 Any thoughts ? Of course I could just discard the TWO smallest and TWO largest ... which might be an option. Thanks for your help Trevor |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula - shorter, neater, faster ?
Thanks Bob, the very thing I was looking for !
I was sure I had seen this or something like it before but couldn't think how to put it together. Cheers ... and thanks for the quick response Trevor "Bob Phillips" wrote in message ... Trevor, How about =(SUM(A15:L15)-(SUM(SMALL(A15:L15,{1,2,3}))+SUM(LARGE(A15:L15,{1, 2,3}))))/6 -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Trevor Shuttleworth" wrote in message ... I'm trying to calculate some targets for next year based on this year's achievements. So, assume I have twelve numbers in a row for January to December. I want to discard the three lowest scores and the three highest scores and then sum the remaining six values and divide by 6 to get an average. I have come up with the following formula but I'm sure there must be a shorter, neater and possibly faster way to write it (well, certainly faster to write with less possibility of getting it wrong !) =(SUM(A15:L15)-(SMALL(A15:L15,1)+SMALL(A15:L15,2)+SMALL(A15:L15,3 )+LARGE(A15 :L15,1)+LARGE(A15:L15,2)+LARGE(A15:L15,3)))/6 Any thoughts ? Of course I could just discard the TWO smallest and TWO largest ... which might be an option. Thanks for your help Trevor |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula - shorter, neater, faster ?
Hi Trevor:
Perhaps the following array formula will do it for you: =AVERAGE(LARGE(A15:L15,ROW(4:9))) array-entered, of course. Regards, Vasant. "Trevor Shuttleworth" wrote in message ... Thanks Bob, the very thing I was looking for ! I was sure I had seen this or something like it before but couldn't think how to put it together. Cheers ... and thanks for the quick response Trevor "Bob Phillips" wrote in message ... Trevor, How about =(SUM(A15:L15)-(SUM(SMALL(A15:L15,{1,2,3}))+SUM(LARGE(A15:L15,{1, 2,3}))))/6 -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Trevor Shuttleworth" wrote in message ... I'm trying to calculate some targets for next year based on this year's achievements. So, assume I have twelve numbers in a row for January to December. I want to discard the three lowest scores and the three highest scores and then sum the remaining six values and divide by 6 to get an average. I have come up with the following formula but I'm sure there must be a shorter, neater and possibly faster way to write it (well, certainly faster to write with less possibility of getting it wrong !) =(SUM(A15:L15)-(SMALL(A15:L15,1)+SMALL(A15:L15,2)+SMALL(A15:L15,3 )+LARGE(A15 :L15,1)+LARGE(A15:L15,2)+LARGE(A15:L15,3)))/6 Any thoughts ? Of course I could just discard the TWO smallest and TWO largest ... which might be an option. Thanks for your help Trevor |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula - shorter, neater, faster ?
"OOhfffff" <g
-- Regards, Tom Ogilvy "Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message ... Hi Trevor: Perhaps the following array formula will do it for you: =AVERAGE(LARGE(A15:L15,ROW(4:9))) array-entered, of course. Regards, Vasant. "Trevor Shuttleworth" wrote in message ... Thanks Bob, the very thing I was looking for ! I was sure I had seen this or something like it before but couldn't think how to put it together. Cheers ... and thanks for the quick response Trevor "Bob Phillips" wrote in message ... Trevor, How about =(SUM(A15:L15)-(SUM(SMALL(A15:L15,{1,2,3}))+SUM(LARGE(A15:L15,{1, 2,3}))))/6 -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Trevor Shuttleworth" wrote in message ... I'm trying to calculate some targets for next year based on this year's achievements. So, assume I have twelve numbers in a row for January to December. I want to discard the three lowest scores and the three highest scores and then sum the remaining six values and divide by 6 to get an average. I have come up with the following formula but I'm sure there must be a shorter, neater and possibly faster way to write it (well, certainly faster to write with less possibility of getting it wrong !) =(SUM(A15:L15)-(SMALL(A15:L15,1)+SMALL(A15:L15,2)+SMALL(A15:L15,3 )+LARGE(A15 :L15,1)+LARGE(A15:L15,2)+LARGE(A15:L15,3)))/6 Any thoughts ? Of course I could just discard the TWO smallest and TWO largest ... which might be an option. Thanks for your help Trevor |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula - shorter, neater, faster ?
Just another option:
=TRIMMEAN(A15:L15,6/12) -- Dana DeLouis Using Windows XP & Office XP = = = = = = = = = = = = = = = = = "Trevor Shuttleworth" wrote in message ... Thanks Bob, the very thing I was looking for ! I was sure I had seen this or something like it before but couldn't think how to put it together. Cheers ... and thanks for the quick response Trevor "Bob Phillips" wrote in message ... Trevor, How about =(SUM(A15:L15)-(SUM(SMALL(A15:L15,{1,2,3}))+SUM(LARGE(A15:L15,{1, 2,3}))))/6 -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Trevor Shuttleworth" wrote in message ... I'm trying to calculate some targets for next year based on this year's achievements. So, assume I have twelve numbers in a row for January to December. I want to discard the three lowest scores and the three highest scores and then sum the remaining six values and divide by 6 to get an average. I have come up with the following formula but I'm sure there must be a shorter, neater and possibly faster way to write it (well, certainly faster to write with less possibility of getting it wrong !) =(SUM(A15:L15)-(SMALL(A15:L15,1)+SMALL(A15:L15,2)+SMALL(A15:L15,3 )+LARGE(A15 :L15,1)+LARGE(A15:L15,2)+LARGE(A15:L15,3)))/6 Any thoughts ? Of course I could just discard the TWO smallest and TWO largest ... which might be an option. Thanks for your help Trevor |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula - shorter, neater, faster ?
Oh, man ... <sigh ... well, at least I enjoyed having the shortest formula
for a few minutes <bg. Nice one, Dana; a new function for me! Regards, Vasant. "Dana DeLouis" wrote in message ... Just another option: =TRIMMEAN(A15:L15,6/12) -- Dana DeLouis Using Windows XP & Office XP = = = = = = = = = = = = = = = = = "Trevor Shuttleworth" wrote in message ... Thanks Bob, the very thing I was looking for ! I was sure I had seen this or something like it before but couldn't think how to put it together. Cheers ... and thanks for the quick response Trevor "Bob Phillips" wrote in message ... Trevor, How about =(SUM(A15:L15)-(SUM(SMALL(A15:L15,{1,2,3}))+SUM(LARGE(A15:L15,{1, 2,3}))))/6 -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Trevor Shuttleworth" wrote in message ... I'm trying to calculate some targets for next year based on this year's achievements. So, assume I have twelve numbers in a row for January to December. I want to discard the three lowest scores and the three highest scores and then sum the remaining six values and divide by 6 to get an average. I have come up with the following formula but I'm sure there must be a shorter, neater and possibly faster way to write it (well, certainly faster to write with less possibility of getting it wrong !) =(SUM(A15:L15)-(SMALL(A15:L15,1)+SMALL(A15:L15,2)+SMALL(A15:L15,3 )+LARGE(A15 :L15,1)+LARGE(A15:L15,2)+LARGE(A15:L15,3)))/6 Any thoughts ? Of course I could just discard the TWO smallest and TWO largest ... which might be an option. Thanks for your help Trevor |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula - shorter, neater, faster ?
Dana
now you are just showing off ! That is so good. I was content with Bob's solution (which is what I had in mind) and impressed by Vasant's solution but this is superb. Thanks very much to everyone for the help and suggestions. I think I'll be able to type this without introducing too many typos. Regards Trevor "Dana DeLouis" wrote in message ... Just another option: =TRIMMEAN(A15:L15,6/12) -- Dana DeLouis Using Windows XP & Office XP = = = = = = = = = = = = = = = = = "Trevor Shuttleworth" wrote in message ... Thanks Bob, the very thing I was looking for ! I was sure I had seen this or something like it before but couldn't think how to put it together. Cheers ... and thanks for the quick response Trevor "Bob Phillips" wrote in message ... Trevor, How about =(SUM(A15:L15)-(SUM(SMALL(A15:L15,{1,2,3}))+SUM(LARGE(A15:L15,{1, 2,3}))))/6 -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Trevor Shuttleworth" wrote in message ... I'm trying to calculate some targets for next year based on this year's achievements. So, assume I have twelve numbers in a row for January to December. I want to discard the three lowest scores and the three highest scores and then sum the remaining six values and divide by 6 to get an average. I have come up with the following formula but I'm sure there must be a shorter, neater and possibly faster way to write it (well, certainly faster to write with less possibility of getting it wrong !) =(SUM(A15:L15)-(SMALL(A15:L15,1)+SMALL(A15:L15,2)+SMALL(A15:L15,3 )+LARGE(A15 :L15,1)+LARGE(A15:L15,2)+LARGE(A15:L15,3)))/6 Any thoughts ? Of course I could just discard the TWO smallest and TWO largest ... which might be an option. Thanks for your help Trevor |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2nd attempt ~ Faster/Shorter formula | Excel Worksheet Functions | |||
Need faster/shorter formula | Excel Worksheet Functions | |||
Can faster CPU+larger/faster RAM significantly speed up recalulati | Excel Discussion (Misc queries) | |||
Shorter Formula | Excel Discussion (Misc queries) | |||
Shorter and faster? | Excel Programming |