LinkBack URL
About LinkBacks
Hi can anyone tell me how count the cells with numbers but ignore the cells with the min and max values and the same to ignore the second large and small value thanks
Min max removed
Hi can anyone tell me how count the cells with numbers but ignore the cells with the min and max values and the same to ignore the second large and small value thanks
If this was your data:
Data Range
What results do you expect?
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Hi,
This will sum A1:A10 excluding both the maximum and minimum values in that range (assuming the maximum and minimum values are unique within that range - you don't say what should be the result if there is more than one maximum or minimum):
=SUMPRODUCT(LARGE(A1:A10,ROW(INDIRECT("2:"&ROWS(A1:A10)-1))))
Regards
Something like:
Formula:
Please Login or Register to view this content.
committed with Ctrl-Shift-Enter rather than just Enter.
You'll see:
Formula:Please Login or Register to view this content.
Regards, TMS
Last edited by TMS; 02-16-2014 at 03:21 PM.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Sorry, Tony. Didn't refresh before posting and so didn't see your post.
No problem!Originally Posted by XOR LX
We need to hear back form the OP.
They ask for a count:
But your formula is doing a sum....tell me how count the cells with numbers...
In this I would want to see count of 5 and if was to include the second part of large and small a count of 3
However I need to also count the numbers below a value so eg the value is 3 so I have 3 cells their but need to ignore the previous section I.e min , and small so my count will 1
So FBI had 44 values I would be left with 40 cells being counted and if I had any cell value which in the case brought up 17 were lower but I had to ignore min and small so this would mean I have 15 lower
Pain in the butt I know thanks for your help
How about this...
Post a SMALL sample file and tell us / show us what cells should be counted.
Definitely a case where we need to see a sample workbook!!
Seems like you have several requirements. Unfortunately, at this moment in time, only you know what the data looks like and what your expected outcomes are.
We can't guess at this ...
EXCEL FORUM HELP.xlsHi guys hope this helps you hlep me
well the first bit for average is
=(SUM(B2:B58)-SUM(LARGE(B2:B58,{1,2}),SMALL(B2:B58,{1,2})))/(COUNT(B2:B58)-4)
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
I don't understand how you can have a "number below minimum"
This is a requirement for an item to be passed if it's lower than min value it is removed as not for purpose
Still confused.
You have all those formulas at the bottom of column B causing circular references.
Maybe someone else will be able to figure it out.
Hi just used them to do the calculation they are hidden normally and the cells at the top are visible this it to tidy up the form as you only have a third of it
Doesn't matter if they're hidden, they'll still cause circular references.
Okay just on the requirements I need and ignoring that area could you help with any of the information I need cheers
Sum of two largest:= 1124Formula:Please Login or Register to view this content.
Sum of two smallest:= 11Formula:Please Login or Register to view this content.
Sum of all entries:= 4035Formula:Please Login or Register to view this content.
Sum of all entries less two largest and two smallest:= 2900Formula:Please Login or Register to view this content.
Count of all entries:= 44Formula:Please Login or Register to view this content.
Count of all entries less two largest and two smallest:= 40Formula:Please Login or Register to view this content.
Average:= 72.5Formula:Please Login or Register to view this content.
Regards, TMS
Last edited by TMS; 02-16-2014 at 06:53 PM.
Number less than minimum:Formula:Please Login or Register to view this content.
Or, with a cell reference:Formula:Please Login or Register to view this content.
Regards, TMS
Excel has a specific function to exclude "outliers" from an average - TRIMMEAN function. You can use that function like this to exclude the top two and bottom two,
=TRIMMEAN(B2:B59,4/COUNT(B2:B59))
Last edited by daddylonglegs; 02-16-2014 at 07:19 PM.
Audere est facere
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks