| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Most frequent item in a list (excluding zeros)
Hello,
I have a range of 12 amounts, and I want to find the most frequent used item but not including the ZERO value A1 0 A2 0 A3 0 A4 0 A5 0 A6 33 A7 17 A8 17 A9 17 A10 0 A11 0 A12 0 I found an array formula in Chip Pearson's site, and it gives me the correct result "0", since it appears 8 times =INDEX(Rng,MATCH(MAX(COUNTIF(Rng,Rng)),COUNTIF(Rng ,Rng),0)) Can this formula be modified so that it EXCLUDES the value ZERO when examining the data?? Thank you in advance. André |
|
#2
|
|||
|
|||
|
Most frequent item in a list (excluding zeros)
Hi!
Try this: Array entered: =MODE(IF(A1:A12<0,A1:A12)) Biff "Andre Croteau" wrote in message ... Hello, I have a range of 12 amounts, and I want to find the most frequent used item but not including the ZERO value A1 0 A2 0 A3 0 A4 0 A5 0 A6 33 A7 17 A8 17 A9 17 A10 0 A11 0 A12 0 I found an array formula in Chip Pearson's site, and it gives me the correct result "0", since it appears 8 times =INDEX(Rng,MATCH(MAX(COUNTIF(Rng,Rng)),COUNTIF(Rng ,Rng),0)) Can this formula be modified so that it EXCLUDES the value ZERO when examining the data?? Thank you in advance. André |
|
#3
|
|||
|
|||
|
Most frequent item in a list (excluding zeros)
Hello Biff,
Thanks, works perfect! André "Biff" wrote in message ... Hi! Try this: Array entered: =MODE(IF(A1:A12<0,A1:A12)) Biff "Andre Croteau" wrote in message ... Hello, I have a range of 12 amounts, and I want to find the most frequent used item but not including the ZERO value A1 0 A2 0 A3 0 A4 0 A5 0 A6 33 A7 17 A8 17 A9 17 A10 0 A11 0 A12 0 I found an array formula in Chip Pearson's site, and it gives me the correct result "0", since it appears 8 times =INDEX(Rng,MATCH(MAX(COUNTIF(Rng,Rng)),COUNTIF(Rng ,Rng),0)) Can this formula be modified so that it EXCLUDES the value ZERO when examining the data?? Thank you in advance. André |
|
#4
|
|||
|
|||
|
Most frequent item in a list (excluding zeros)
You're welcome. Thanks for the feedback!
Biff "Andre Croteau" wrote in message ... Hello Biff, Thanks, works perfect! André "Biff" wrote in message ... Hi! Try this: Array entered: =MODE(IF(A1:A12<0,A1:A12)) Biff "Andre Croteau" wrote in message ... Hello, I have a range of 12 amounts, and I want to find the most frequent used item but not including the ZERO value A1 0 A2 0 A3 0 A4 0 A5 0 A6 33 A7 17 A8 17 A9 17 A10 0 A11 0 A12 0 I found an array formula in Chip Pearson's site, and it gives me the correct result "0", since it appears 8 times =INDEX(Rng,MATCH(MAX(COUNTIF(Rng,Rng)),COUNTIF(Rng ,Rng),0)) Can this formula be modified so that it EXCLUDES the value ZERO when examining the data?? Thank you in advance. André |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Creating an invoice with a lookup list | Excel Discussion (Misc queries) | |||
| Average, Excluding Zeros, Non-Consecutive Range | Excel Discussion (Misc queries) | |||
| Data Validation lists - entering value not in list | Excel Discussion (Misc queries) | |||
| Jump to the item in a list as the first letter is pressed | Excel Discussion (Misc queries) | |||
| Extracting Values on one list and not another | Excel Discussion (Misc queries) | |||
Linear Mode
