Formulas not calculating correctly

EvelynTN

New Member
Joined
Aug 30, 2019
Messages
5
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
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,939
Office Version
2007
Platform
Windows
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.
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,737
Office Version
2010
Platform
Windows
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.
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,939
Office Version
2007
Platform
Windows
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

Well-known Member
Joined
Mar 2, 2014
Messages
2,737
Office Version
2010
Platform
Windows
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

New Member
Joined
Aug 30, 2019
Messages
5
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

Well-known Member
Joined
Dec 3, 2018
Messages
11,939
Office Version
2007
Platform
Windows
In AP3936 the result is 1 and it is correct!

 

EvelynTN

New Member
Joined
Aug 30, 2019
Messages
5
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

Well-known Member
Joined
Dec 3, 2018
Messages
11,939
Office Version
2007
Platform
Windows
I'm confused, did it work for you?
 

Watch MrExcel Video

Forum statistics

Threads
1,100,142
Messages
5,472,754
Members
406,835
Latest member
steve43040

This Week's Hot Topics

Top