• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Having trouble counting using arrays

MSC Bobs

MSC Bobs

Member
Hey everyone,

I’m having trouble doing something that used to be very easy for me. I’ve spent all day on the problem and haven’t been able to solve it and I would really appreciate some help.

The problem is that the counting formulas I like to use for some reason are not working. My preferred method of counting is to use IF statements nested in SUM so that I can filter.

I’m attaching two workbooks. The first one is called Programmer Validation. I purchased a data source and before the programmers were finished they sent me some sample files so that I could validate the data. On the calculation tab, you can see all of the different basketball statistics I’m tracking. I created quick and easy drop downs in cells B2 and B4 and then the matrix below updates. Beginning in cell B11 and going down, you can see how I use single-cell arrays with nested SUM(IF(

After I verified my data source was correct in the Programmer Validation workbook, I received the actual data, which is in the workbook Copy of Master Games. All of a sudden, nothing works.

Not even my normal trouble shooting is working. To trouble shoot, I replace references with hard codes and once I validate that, then I paste in the reference.

I’d appreciate if you guys can help me figure out what happened and why it’s not working anymore.

Thanks.
 

Attachments

Shot Type is Text and not Numbers
So your formula should be <>"4"
or change the Shot Type to Numbers

How?
Type a 1 in a blank cell
select the cell and Copy
Select teh Shot Type Column
Paste, Paste Special, Values, Multiply
 
Hey Hui,

Thanks for the response. It helped narrow down the problem. I went back to the programmers and somehow the formatting had been changed in a way that even me using CTRL+1 to manually make everything congruent wouldn't have mattered.

I'm glad to know to I'm not crazy.

Thanks again.
 
The give away is that Numbers are Typically Right Justified
Yours were Left Justified as Text is

Ctrl+1 or Custom Formatting only displays how the numbers are cell contents are displayed, it doesn't change the underlying values
 
The programmers have fixed the error, now that it's been identified (thankfully). If this ever happens again, is there a way for me to correct the formatting?
 
Create helper column somewhere on the sheet.
=Value(Cell Ref)

And then copy and paste values back on the target column.
 
You must log in or register to reply here.
Back
Top