Formulas Work Only One Line and Not Any Copied Down Below

[brenda1996]

Hi again,

Looking for some help as to why two formulas I have in my spreadsheet work perfect as intended but anything below that one line I copied down to will not.

IF(AND(C3=365,OR(D3="MON",D3="YR",D3="D")),"SHOULD BE DAY","") and IF(AND(C3<>365,OR(D3="DAY",D3="D")),"SHOULD BE MON or YR","") are in separate columns.

When I play with the data in columns C and D I get the expected results for only that one line. Formulas are the same copied down with the exception of line number.

The only way I can get formulas to work was to copy first line entirely twice below it.

In the snippet the first 3 rows are expected results, everything below is not and each cell was copied down from the first row.

The last four rows Columns E and F should both be blank and in first row. The fourth row should be the same as the second row.

Any help is sincerely appreciated as I have run into a dead end and about fixing to start pulling my hair out!

Brenda

 

[steve the fish]

If be checking your cells where your result is not as expected. Are there trailing spaces etc after "DAY"?

 

[brenda1996]

If be checking your cells where your result is not as expected. Are there trailing spaces etc after "DAY"?

Hi Steve. No trailing spaces ...

 

[brenda1996]

Hi Steve. No trailing spaces ...

Actually I can delete the data and type in DAY and the formula does not work for those copied down. I do the same for the one line that does work and it works!

 

[steve the fish]

Ok split it out. Test these:

=C3=365
=OR(D3="MON",D3="YR",D3="D")

To give you a clue about where the problem lies. The formulas themselves dont look to be the problem. Looks a problem with the data in C or D

 

[brenda1996]

Ok split it out. Test these:

=C3=365
=OR(D3="MON",D3="YR",D3="D")

To give you a clue about where the problem lies. The formulas themselves dont look to be the problem. Looks a problem with the data in C or D

I just tried the CLEAN function on the data and that usually takes care of issues but had no effect this time, same result. trying to figure out how I should test with your direction.

 

[steve the fish]

In any spare cell. Looks like G3 & H3 would be good candidates. Then drag down. If you think your data could be 'dirty' then also try =LEN(C3) and =LEN(D3). Do they give expected numbers? That is does 365 produce 3 for example.

 

[brenda1996]

In any spare cell. Looks like G3 & H3 would be good candidates. Then drag down. If you think your data could be 'dirty' then also try =LEN(C3) and =LEN(D3). Do they give expected numbers? That is does 365 produce 3 for example.

I get a True and a False when using those formulas in G and H.

 

[brenda1996]

Len returns 3 for 365 and 3 for DAY also.

 

[steve the fish]

So is that unexpected? I cant see your sheet dont forget. You should be getting lots of TRUE and FALSE if you are testing the sample you gave. What you are looking for is where you see an unexpected result. Then test that cell with the LEN formula.