I was making a histogram today, and this pc doesn't have the Analysis Toolkit and for whatever reason I didn't feel like tracking it down and installing it.
So I found myself with a column of raw data and a column of bin values to use, and I wrote this:
=COUNTIFS($A$1:$A$10,CONCATENATE(">=", $B1),$A$1:$A$10,CONCATENATE("<", $B2))
In other words, count the cells in my raw data range if they are above the lower bin and below the next one.
This did not work - every count returns zero.
If, instead, I pulled the concat into its own column (so Bins are in B and I have in C :
CONCATENATE(">=", B1) and in column D I have
CONCATENATE("<",B1) and then edit the histogram formula to reference the new intermediates, the Countifs works again.
=COUNTIFS($A$1:$A$10, C1 ,$A$1:$A$10, D2)
I feel like there is some lesson about how the Excel formula parser is working under the hood in this that I should have internalized so it doesn't sneak up and kill me one day. Does anyone understand what's actually going on here?
Ok, well, Excel wants to make a monkey of me. Now this is working .. and I don't know of anything I did to make it switch from not-working (Which, I swear it was) to working. Maybe asking a question on the internet has some Spooky Action At a Distance type property. I suppose its possible that the cells were confused about being text as left over from the text to columns call and eventually they realized they were numbers? I'm baffled. Sorry for the goose chase!