Formula stopped working

[spydey]

So I am pulling out my hair here, trying to figure out why over the last two months, this formula has been working, and now it stopped. I have duplicated my previous work to the letter, I have double, triple, and quadruple checked my data sets. I have named and re-named my ranges to ensure they match previous work & the formula references, I have started from scratch working up, and nothing seems to make any sense as to why it isn't working now.

My formula is this (confirmed with CSE):

=if(countifs(ID_Range,$A3,Location_Range,$B3)=0,"N/A",countifs(ID_Range,$A3,Location_Range,$B3,Date1_Range,">="&Date2_Range)/countifs(ID_Range,$A3,Location_Range,$B3))

Again, I confirm with CSE (Control + Shift + Enter), then fill down.

It is spitting out all 0s (zeros). All data is found in the same workbook, just on different sheets, thus the use of ranges.

If I break the equation up into it's three parts: logical test, true, & false, I believe that my issue is stemming from the numerator of the false section.

Logical Test & true part:

=if(countifs(ID_Range,$A3,Location_Range,$B3)=0,"N/A"

This works. For any ID with Location that is not present, it is outputting "N/A", as expected.

False part:

I broke the False section up into its two parts: the numerator & denominator.

The denominator gives back as an output the number of items there are for each ID & Location match, as expected and needed.

The denominator is spitting back 0 for everything. I narrowed it down to: Date1_Range,">="&Date2_Range.

This has worked before, although I did run into something strange previously with it.

I used to have it has: Date2_Range,"<="&Date1_Range. It work for a month, then stopped working. So I switched them to what I have above, and it worked fine, until now.

Now either way I try it, I still get the same output of 0 (zero).

Logically, as far as I can tell, the formula makes sense. So that must mean that it is a data issue.

I check all dates in each date range and they are all valid dates. I check what would be the corresponding ID & Location for the data where Date1 & Date 2 are found, they are good too.

I just can't figure out why this isn't working now after 2 months of it working fine.

Any ideas, pointers, thoughts, suggestions, etc., is greatly appreciated!!

-Spydey

P.S. I don't have much hair left now .... my wife is going to kill me! She doesn't like me going bald ... hahahahahahaha

 

[Jonmo1]

Re: Formula stopped working ..... going bald!!

What is the actual range references for Date1_Range and Date2_Range ?

If those are BOTH multicell ranges, then it's not going to work that way.
The criteria can only be a single cell/value.

I think you'll need a helper column to do =B2>=A2 and fill down.
Then use
=if(countifs(ID_Range,$A3,Location_Range,$B3)=0,"N/A",countifs(ID_Range,$A3,Location_Range,$B3,Helper_Range,TRUE)/countifs(ID_Range,$A3,Location_Range,$B3))

 

[spydey]

Re: Formula stopped working ..... going bald!!

What is the actual range references for Date1_Range and Date2_Range ?

If those are BOTH multicell ranges, then it's not going to work that way.
The criteria can only be a single cell/value.

I think you'll need a helper column to do =B2>=A2 and fill down.
Then use
=if(countifs(ID_Range,$A3,Location_Range,$B3)=0,"N/A",countifs(ID_Range,$A3,Location_Range,$B3,Helper_Range,TRUE)/countifs(ID_Range,$A3,Location_Range,$B3))

I can give that a tray.

I just find it curious (and frustrating) that it was working for the last two months. I would manually verify the ratios to ensure that it was working correctly. It is only as of today that it stopped working. I don't know of any other items that have changed in my range, etc.

I will let you know what I come back with.

Thanks Jonmo1

-Spydey

 

[Jonmo1]

Re: Formula stopped working ..... going bald!!

I believe this was a false assumption

This has worked before, although I did run into something strange previously with it.

I think your conclusion that it was working is simply because it gave a non zero number as a result.
But it was probably not the 'correct' number. Or if it was correct, it was purely coincidental.
That formula is not doing what I believe you think it does.
But I can't be certain without you telling me the actual real ranges that your names refer to. Can you post that?

 

[spydey]

Re: Formula stopped working ..... going bald!!

@ Jonmo1,

So I just went back to my previous 2 months worth of data and manually compared the formula's output to the actual manually calculated ratios. They match up 100% of the time. So this leads me to believe that the formula was working for all 100+ iterations, for each month, so a total of 200+ times the formula was used and 100% correct each time.

Here are the locations:

Formula is located on sheet 1, cell C3.

The $A3 & $B3 that are referenced in the formula are found in sheet1.

Sheet1 Column A (starting with A3) contains 65 unique IDs, from A3:A103, thus some are duplicates.
Sheet1 Column B (starting with B3) contains 55 unique locations, from B3:B103, thus some are duplicates.

ID_Range is Sheet2 A2:A258
Location_Range is Sheet2 B2:B258

Date1_Range is C2:C258
Date2_Range is D2:D258

So basically, it is supposed to count where ID & Location on Sheet 2 are the same as on sheet 1 and where Date1 >= Date2 / count where ID & location on sheet 2 are the same as on sheet 1.

I hope that is what you were asking for Jonmo1.

If not, I will try and provide a dummy data set later.

-Spydey

 

[Jonmo1]

Re: Formula stopped working ..... going bald!!

ok, so looking at just the date criteria, and reducing it to say 4 rows for this example

=COUNTIFS(C2:C5,">="&D2:D5)

Are you expecting it to count
IF C2 > D2
PLUS
IF C3 > D3
PLUS
IF C4 > D4
PLUS
IF C5 > D5


That's NOT what the formula does (even with the CTRL + SHIFT + ENTER)
The 2nd argument (the criteria) can only be a single cell / value.
Putting a multicell range there, excel really only uses one cell of that range.
And I believe it uses the cell in the same row as the cell you put the formula in (this probalby explains the Strange Thing you mentioned happening)

So if the formula is in Row 2, This is what it actually does.
COUNT
IF C2 > D2
PLUS
IF C3 > D2
PLUS
IF C4 > D2
PLUS
IF C5 > D2

 

[spydey]

Re: Formula stopped working ..... going bald!!

ok, so looking at just the date criteria, and reducing it to say 4 rows for this example

=COUNTIFS(C2:C5,">="&D2:D5)

Are you expecting it to count
IF C2 > D2
PLUS
IF C3 > D3
PLUS
IF C4 > D4
PLUS
IF C5 > D5


That's NOT what the formula does (even with the CTRL + SHIFT + ENTER)
The 2nd argument (the criteria) can only be a single cell / value.
Putting a multicell range there, excel really only uses one cell of that range.
And I believe it uses the cell in the same row as the cell you put the formula in (this probalby explains the Strange Thing you mentioned happening)

So if the formula is in Row 2, This is what it actually does.
COUNT
IF C2 > D2
PLUS
IF C3 > D2
PLUS
IF C4 > D2
PLUS
IF C5 > D2

I believe that you are correct sir. I testing something out. I should have mentioned that my ranges for date1 & date2 this time around included text headers, when last time they didn't. They included just the dates. It just dawned on me this morning.

So I changed my ranges to not include the text headers. And I now have ratios!!

But I was curious about what you mentioned above so I put it to the test.

I took my sheet two data sets and copied them over to a new workbook.

Then I re-did my formula to reference the new workbook for the data1 & date 2. I didn't name them as ranges this time. It is was just for a test so no need.

Having my main workbook sheet1 up which shows me the output of the formula, and having the test workbook with sheet2 data up, side-by-side allowed me to manipulate the test data and see if it affected the main workbook output at all. If the formula worked as I thought/hoped it did, then the output shouldn't change, no matter how I sort/filter the test data, as long as the test data is the same.

Well, it did change. I would sort date1 descending .... the output changed. I would date 2 by descending .... the output changed. I would sort other columns in the test workbook and the output would change.

Which leads me to realize that you are correct Jonmo1. The formula is only using the first cell of the date2 range ... whichever cell that happens to be. Which explains why when I was including my text headers in the range, they being at the top, I was getting 0s (zer0s) every time. Because you can't logically express one word as greater than another work (unless you count the characters, etc etc etc).

So my previous two months of data, while correct, as I manually verified them, did not have a correct formula in them.

I have hence inserted a helper column and will move forward with that.

I would like to thank you Jonmo1 for your time and assistance in this matter. Walking through it with you, bouncing ideas off and listening to your input was most helpful. Thank you again.

Take care and I look forward to any possible future input you might have on any more questions that I know I will have, hehehe.

-Spydey

 

[Jonmo1]

Re: Formula stopped working ..... going bald!!

You're welcome.

And just an opinion here, Helper columns are NOT a bad thing (that's why they're called Helper).
Excel gives you over 16 thousand columns (xl2007+ anyway), might as well use a few of them.
So I wouldn't consider this a step back. If anything, it probably makes the calculation happen faster.