#VALUE! Error

Nanaia

Active Member
Joined
Jan 11, 2018
Messages
306
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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]
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Forum statistics

Threads
1,223,888
Messages
6,175,206
Members
452,618
Latest member
Tam84

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top