LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.misc
ace ace is offline
external usenet poster
 
Posts: 19
Default Sumproduct question

Guys,

I have a question regarding the sumproduct formula.
Here is the situation;
considering the following data set;
$0.00 (D66)
$0.00 (D67)
$14.00
$18.75
$18.25
$20.00
$20.00 (D72)
I am computing the adjusted average for the above data set discounting zeros
and the max outlier.
My formula is

SUMPRODUCT((D66:D720)*(D66:D72<MAX(D66:D72))*D66: D72)/SUMPRODUCT((D66:D720)*(D66:D72<MAX(D66:D72)))

In the situation described above, it must take 14 as the outlier and discard
as that is more qualitatively appropriate.
So my question is, can the sumproduct check for the max and min outlier,
discard whichever is farthest from the simple average (simple average too
discards zeros), discard zeros, and then give me the adjusted average???
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
SumProduct Question MrRJ Excel Discussion (Misc queries) 4 February 6th 09 07:14 PM
SumProduct Question Gina[_2_] Excel Discussion (Misc queries) 4 August 25th 08 09:42 PM
Sumproduct question Chris Excel Discussion (Misc queries) 1 February 5th 08 12:33 AM
Sumproduct Question Sandy Excel Worksheet Functions 4 October 13th 07 12:45 PM
Sumproduct ?? Question carl Excel Worksheet Functions 3 March 16th 06 06:46 PM


All times are GMT +1. The time now is 12:10 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"