Formulas not calculating correctly

[EvelynTN]

Help! I have spreadsheet in excel, and a few of the cells are not calculating correctly. My worksheet has 4,000+ rows, and one column has the following formula:

=IF(AL4051="D",IF(+T4051=1,COUNTIFS(AL$2:AL4051,"D",U$2:U4051,"<3",A$2:A4051,A4051,T$2:T4051,1),0),0)

The formula calculated correctly for all but 6 of the cells. For these cells, it tripped up in the column U value for the row. The 6 cells had "3" as the value in column U (and all other conditions to be counted met), and it returned a "1" instead of a "0".

I read about 'dirty cells'. I fixed three of the formulas by replacing the equals sign (click and dragging the formula didn't work). Same formula, but apparently triggered it to recalculate. Then I saved the worksheet. I went to fix the remaining three formulas, and replacing the equals sign didn't work on them.

Even if I can fix these remaining 3 cells, there is a count on another sheet that isn't updating after the 3 fixed cells were changed. Help!

Is there a way to globally fix this dirty cell problem? I really don't want the manually check every time the worksheet is updated. i am using Excel 2016.

Thanks in advance, Evelyn

 

[DanteAmor]

You have to check the data in your cell, maybe they have spaces or are numbers but entered as text.
If you can upload a file to the cloud to review it.

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[EvelynTN]

Here is the dropbox link:
https://www.dropbox.com/s/za00exra104fcxt/Prior Year for Dropbox.xlsx?dl=0

The cells that are calculating incorrectly are: AP3967 an AP4035 on worksheet 'Appt'. It appears the formulas are misreading cells U3967 and U4035

And the cells on the summary page are calculating correctly.

Thanks in advance for your help!

 

[joeu2004]

The cells that are calculating incorrectly are: AP3967 an AP4035 on worksheet 'Appt'. It appears the formulas are misreading cells U3967 and U4035

No.

For AP3967, it is the cell values in row 3174 that cause COUNTIFS to return 1. AL3174="D", U3174<3 (2), A3174=A3967 ("YA038") and T3174=1.

For AP4035, it is the cell values in row 3711 that cause COUNTIFS to return 1. AL3711="D", U3711<3 (2), A3711=A4035 ("HO225") and T3711=1.

I found these by array-entering the following formulas (press ctrl+shift+Enter instead of just Enter):

BD3174: =MATCH(TRUE,(AL$2:AL3967="D")*(U$2:U3967<3)*(A$2:A3967=A3967)*(T$2:T3967=1)=1,0)
BD4035: =MATCH(TRUE,(AL$2:AL4035="D")*(U$2:U4035<3)*(A$2:A4035=A4035)*(T$2:T4035=1)=1,0)

Note that the MATCH return value is relative to row 2. For example, 3710 is actual row 2+3710-1.

-----
PS.... Unrelated observations (not errors).

Why do you write +T3174? The unary plus does nothing. If your intent is to allow for and convert a numeric string in T3174, you should write 0+T3174 or --T3174.

In your posting, you quote numbers; e.g., "3", "1", "0" [sic]. That is a bad habit that can lead to mistakes in formulas (which you did not make this time). In the future, write numbers as numbers (3, 1, 0), not strings.

 

[DanteAmor]

The formula in AP3967 is correct:

The result is 1 and is formed in this way:

AL = "D"
U < 3
A = "YA038"
T= 1

Or am I missing something?

 

[joeu2004]

I found these by array-entering the following formulas (press ctrl+shift+Enter instead of just Enter):
BD3174: =MATCH(TRUE,(AL$2:AL3967="D")*(U$2:U3967<3)*(A$2:A3967=A3967)*(T$2:T3967=1)=1,0)
BD4035: =MATCH(TRUE,(AL$2:AL4035="D")*(U$2:U4035<3)*(A$2:A4035=A4035)*(T$2:T4035=1)=1,0)

Or better: you could use filtering, as DanteAmor demonstrated. Klunk!

 

[EvelynTN]

My apologies - it was in cellsAP3936 and AP4035 that I was not liking the calculations. I changed theformula to bring one of the countifs to anif:=IF(AL3936="D",IF(T3936=1,IF(U3936<3,COUNTIFS(AL$2:AL3936,"D",A$2:A3936,A3936,T$2:T3936,1),0),0),0) I think it is working the way I want it to; I will continue to investigate.

Regarding unrelated observations. When I am building aformula from inside out, I'll start with +before the cell address. Surprisingly,it doesn't trip me up.

The way I write with words and the way I write in excel are 2different things. I would never put quotes around a number inexcel. Surprisingly, I don't, and it doesn't trip me up.

You can find all sorts or errors in this notealso. I apologize for my confusingcommunications! I’m outta here-

 

[DanteAmor]

In AP3936 the result is 1 and it is correct!

 

[EvelynTN]

Hi Dante,
Thanks for hanging in there with me! For Pt WO0078, I wanted 1 in column ap, row3154 and 0 in column ap, row 3936. Iwanted a 1 in row 3154 because it was the first countable cleaning in thequarter (but second countable cleaning in the year). I wanted the 0 in row 3936 because it was thethird cleaning of the year. There is no reimbursement for cleanings over thesecond one in a calendar year so it wouldn’t count in the quarter. I moved the condition yearly cleaning “<3” to become an if statement rather than in the countifsstatement. It seems to be working now!
Unrelated Observaton---------------
I took out the quotes in the paragraph above (except for “<3”because that how you write it in excel). I’m sure it make more sense now!

 

[DanteAmor]

I'm confused, did it work for you?