2

We have a large spreadsheet that we use to calculate performance for race car drivers. It has been stable for quite some time. Today, I opened it and found that one of the tables was not calculating correctly. I tried recalculating the sheet (it is set to manual calc), and tried rebuilding the tree (ctl+alt+shift+f9) to no avail. Other formulas referencing the same named range function correctly as do other formulas using average if.

Variables

list_of_names = A list of first and last names in a single text string imported from a CSV file

local_name = A name (100% guaranteed to be included in list_of_names) to calculate an average of a drivers performance in a given sector of the track

sector_percent = A percentage of a driver's trips through a particular sector that fall into a pre-determined range

sector_count = The number of trips the driver makes though a sector

My original formula returns a #Value error. This is the original formula (the actual formula contains an IFERROR statement, but I have removed it here for clarity. The #VALUE error happens either way).

{=AVERAGEIFS(sector_percent,list_of_names,local_name,sector_percent,">0",sector_count,">"&min_number_sectors)}

After some experimenting, I have found that the following formula successfully reports the correct answer:

{=AVERAGE(IF(list_of_names=local_name,IF(sector_percent>0,IF(sector_count>min_number_sectors,sector_percent,0))))}

If you strip the list_of_names and local_name variables from the AVERAGEIFS formula, it behaves correctly (given the data that meets the criteria). This led me to believe that the list of names and the local name were not of a matching data type. However the #VALUE error still occurs if both are set to general or text. TYPE(list_of_names) or TYPE(local_name) both return 2 presently. {TYPE(list_of_names)} returns 64 as it should.

The sheet is able to perform the list_of_names to local_name function correctly in other places in the workbook and in other areas of the same sheet.

I have tried:

-Replacing all named ranges with the actual cells referred to by the name in the formula

-Referring to different local_names in the list_of_names

-INDEX(list_of_names,ROW(A1)) correctly reports the list of names when you drag it out.

-Various orders of criteria, using other criteria.

-A number of other heat of the moment changes that I can't currently recall

Essentially, the list_of_names to local_name comparison fails in this area of the sheet every time using AVERAGEIFS where AVERAGE(IF( does not.

To me the formula is correct either way, but the sudden failure in this one part of the sheet is odd.

This is my first post here and I would appreciate any help that is available. Hopefully, I have provided enough information to lead to an answer. If not, let me know and I will fill in any gaps.

RSR_john
  • 51
  • 1
  • 6
  • 4
    Are the cell ranges the same size? The `#VALUE!` error often occurs when different size ranges are passed into the [AVERAGEIFS function](https://support.office.microsoft.com/en-us/article/AVERAGEIFS-function-4cca2550-dab3-41c4-bcd2-648e17f20553). –  Feb 18 '15 at 19:10
  • 2
    Agreed - that would be my diagnosis too - You absolutely can't have mismatched range sizes in `AVERAGEIFS`......but array formulas can still function with ranges that are different sizes (given the right circumstances) – barry houdini Feb 18 '15 at 19:28
  • Share you actual formula? – guitarthrower Feb 18 '15 at 20:13
  • 2
    Note that if you really have that zero right at the end of the AVERAGE(IF array formula that will potentially skew your average, because in all cases where the first two criteria are satisfied but the third is not it will add a zero to the values to be averaged - I recommend you remove the **,0** – barry houdini Feb 18 '15 at 20:36
  • Here is the original non - working formula [link]http://imgur.com/ChvJ4nq – RSR_john Feb 18 '15 at 20:58
  • @barryhoudini The zero was just a placeholder for the FALSE value. When I tested the code I used a text string that was perhaps not fit for publication. – RSR_john Feb 18 '15 at 21:02

1 Answers1

3

Both @barryhoudini and @Jeeped are correct. I had failed to drag the information in one of the source tables far enough creating a size mismatch in the range sizes. I can't figure out how to accept that as an answer other than to answer it myself, which would not apply the proper credit where it is certainly due. I thank you both for the assistance, it was concise and excellent. I still cannot understand why one formula works and one does not. Is it possible that the AVERAGE(If has a less restrictive set of constraints when it comes to range size?

RSR_john
  • 51
  • 1
  • 6
  • 2
    You can't use an array formula like this: `=AVERAGE(IF(A2:A9="x",B2:B10))`....where the range to average is bigger than the criteria range (you get #N/A error), but this version works to an extent `=AVERAGE(IF(A2:A10="x",B2:B9))` - it only fails if A10 = "x" - so your formula was probably along the lines of that second one – barry houdini Feb 18 '15 at 21:25
  • 1
    This seems close enough to an answer to the be accepted answer. Wait 48 hours and you can accept the answer yourself. +1 from me for getting to the root of the problem (albeit with a little prodding!) :) see [Can I answer my own question?](http://stackoverflow.com/help/self-answer) –  Feb 18 '15 at 21:30
  • 2
    Finally had a look at the image of the formula(s) you provided. If the named ranges are resized on a regular basis due to fluctuating staff, I would suggest defining them with formulas. Example: if *CR_Driver_In* had a refers to of Sheet1!CC3:** then the Refers to: could be **=Sheet1!CC3:INDEX(Sheet1!CC:CC, MATCH("zzz", Sheet1!CC:CC))**. *SR_S01_Percent* would be something like **=Sheet1!CD3:INDEX(Sheet1!CD:CD, MATCH("zzz", Sheet1!CC:CC))**. By using the same row limiter on different columns you should never run into this again. –  Feb 18 '15 at 22:37
  • @Jeeped Currently we are using the following in the refers to: **=SHEET1!$B$2:OFFSET(SHEET1!$B$2,0,0,COUNTA(SHEET1!$B$2:$B$5000),1)**. The idea being that we won't have more than 5000 rows of data which has been the case so far, but I was looking to make the name generation more robust. I will give it a try. Thanks! – RSR_john Feb 19 '15 at 18:54