I’ve created a template file for us to use that we copy & paste a CSV for every job. Most of it is locked (per boss’ request). In this template file I have a table that looks at the most common lengths in one column of the CSV. The formula’s you guys gave work great, but there’s a snag that I haven’t figured out the cause of and I was wondering if I could get some help from you to eliminate the problem. In the most common lengths table the formula I use for the 2nd, 3rd, 4th, and 5th most common lengths frequently give a #VALUE! Error. It’s inconsistent as far as which one gives the error. Sometimes it’s only one, sometimes more than one, sometimes it’s all of them. If the source table was unlocked, resolving the error is as simple as clicking in the cell with the error and hitting Ctrl+Shift+Enter, but since the cells are locked and my coworkers aren’t comfortable going into the cell to hit those keys so it’s not that simple. Is there another way to write the formula to prevent getting that error?
Here is the table I’m referring to. (I hope it copies okay) It’s located M19:N28. The numbers in CSV column S range from 12.5 to 81.209 and column T ranges from 6 to 90.64. I can give you the exact list of numbers if needed. Just let me know. Thank you in advance for your help.
[TABLE="width: 179"]
<tbody>[TR]
[TD="colspan: 2"]Panel Lengths
[/TD]
[/TR]
[TR]
[TD]=MODE(CSV!S3:S501)
[/TD]
[TD]1st most common
[/TD]
[/TR]
[TR]
[TD]=MODE(IF(COUNTIF($M$20:$M20,CSV!$S$3:$S$501)=0,IF(ISNUMBER(CSV!$S$3:$S$501),CSV!$S$3:$S$501*{1,1})))
[/TD]
[TD]2nd most common
[/TD]
[/TR]
[TR]
[TD]=MODE(IF(COUNTIF($M$20:$M21,CSV!$S$3:$S$501)=0,IF(ISNUMBER(CSV!$S$3:$S$501),CSV!$S$3:$S$501*{1,1})))
[/TD]
[TD]3rd most common
[/TD]
[/TR]
[TR]
[TD]=MODE(IF(COUNTIF($M$20:$M22,CSV!$S$3:$S$501)=0,IF(ISNUMBER(CSV!$S$3:$S$501),CSV!$S$3:$S$501*{1,1})))
[/TD]
[TD]4th most common
[/TD]
[/TR]
[TR]
[TD]=MODE(IF(COUNTIF($M$20:$M23,CSV!$S$3:$S$501)=0,IF(ISNUMBER(CSV!$S$3:$S$501),CSV!$S$3:$S$501*{1,1})))
[/TD]
[TD]5th most common
[/TD]
[/TR]
[TR]
[TD]=MAX(CSV!S3:S501)
[/TD]
[TD]Longest
[/TD]
[/TR]
[TR]
[TD]=MIN(CSV!S3:S501)
[/TD]
[TD]Shortest
[/TD]
[/TR]
[TR]
[TD]=MAX(CSV!T3:T501)
[/TD]
[TD]Widest
[/TD]
[/TR]
[TR]
[TD]=MIN(CSV!T3:T501)
[/TD]
[TD]Narrowest
[/TD]
[/TR]
</tbody>[/TABLE]
Here is the table I’m referring to. (I hope it copies okay) It’s located M19:N28. The numbers in CSV column S range from 12.5 to 81.209 and column T ranges from 6 to 90.64. I can give you the exact list of numbers if needed. Just let me know. Thank you in advance for your help.
[TABLE="width: 179"]
<tbody>[TR]
[TD="colspan: 2"]Panel Lengths
[/TD]
[/TR]
[TR]
[TD]=MODE(CSV!S3:S501)
[/TD]
[TD]1st most common
[/TD]
[/TR]
[TR]
[TD]=MODE(IF(COUNTIF($M$20:$M20,CSV!$S$3:$S$501)=0,IF(ISNUMBER(CSV!$S$3:$S$501),CSV!$S$3:$S$501*{1,1})))
[/TD]
[TD]2nd most common
[/TD]
[/TR]
[TR]
[TD]=MODE(IF(COUNTIF($M$20:$M21,CSV!$S$3:$S$501)=0,IF(ISNUMBER(CSV!$S$3:$S$501),CSV!$S$3:$S$501*{1,1})))
[/TD]
[TD]3rd most common
[/TD]
[/TR]
[TR]
[TD]=MODE(IF(COUNTIF($M$20:$M22,CSV!$S$3:$S$501)=0,IF(ISNUMBER(CSV!$S$3:$S$501),CSV!$S$3:$S$501*{1,1})))
[/TD]
[TD]4th most common
[/TD]
[/TR]
[TR]
[TD]=MODE(IF(COUNTIF($M$20:$M23,CSV!$S$3:$S$501)=0,IF(ISNUMBER(CSV!$S$3:$S$501),CSV!$S$3:$S$501*{1,1})))
[/TD]
[TD]5th most common
[/TD]
[/TR]
[TR]
[TD]=MAX(CSV!S3:S501)
[/TD]
[TD]Longest
[/TD]
[/TR]
[TR]
[TD]=MIN(CSV!S3:S501)
[/TD]
[TD]Shortest
[/TD]
[/TR]
[TR]
[TD]=MAX(CSV!T3:T501)
[/TD]
[TD]Widest
[/TD]
[/TR]
[TR]
[TD]=MIN(CSV!T3:T501)
[/TD]
[TD]Narrowest
[/TD]
[/TR]
</tbody>[/TABLE]