Grammarjunkie
Board Regular
- Joined
- Mar 22, 2016
- Messages
- 86
Code:
=IF(AND(D12="Fixed",D26<>J75,G50=J75,OR(D26-J75>0.125%,D26-J75<-0.125%)),CONCATENATE("APR difference between ELC (",TEXT(D26,"0.0000%"),") and Final CD (",TEXT(J75,"0.000%"),") not within tolerance"),IF(AND(D12="Fixed",D26<>J75,G50=J75,OR(D26-J75<0.125%,D26-J75>0.125%)),CONCATENATE("APR difference between ELC (",TEXT(D26,"0.0000%"),") and Final CD (",TEXT(J75,"0.000%"),") not equal but within tolerance"),IF(AND(D12="Fixed",D26<>J75,J75>G50,J75-G50>0.125%,OR(D26-J75>0.125%,D26-J75<-0.125%)),CONCATENATE("APR difference between ELC (",TEXT(D26,"0.0000%"),") and Final CD (",TEXT(J75,"0.000%"),") not within tolerance, and difference between Final CD (",TEXT(J75,"0.000%"),") and Preliminary CD (",TEXT(G50,"0.000%"),") not within tolerance"),IF(AND(D12="Fixed",D26<>J75,J75>G50,J75-G50>0.125%,OR(D26-J75<0.125%,D26-J75>-0.125%)),CONCATENATE("APR difference between ELC (",TEXT(D26,"0.0000%"),") and Final CD (",TEXT(J75,"0.000%"),") within tolerance, and APR difference between Final CD (",TEXT(J75,"0.000%"),") and preliminary CD (",TEXT(G50,"0.000%"),") not within tolerance."),IF(AND(D12="Fixed",D26<>J75,J75>G50,J75-G50<0.125%,OR(D26-J75>0.125%,D26-J75<-0.125%)),CONCATENATE("APR difference between ELC (",TEXT(D26,"0.0000%"),") and Final CD (",TEXT(J75,"0.000%"),") not within tolerance, and difference between Final CD (",TEXT(J75,"0.000%"),") and Preliminary CD (",TEXT(G50,"0.000%"),") not within tolerance"),IF(AND(D12="Fixed",D26<>J75,J75>G50,J75-G50<0.125%,OR(D26-J75<0.125%,D26-J75>-0.125%)),CONCATENATE("APR difference between ELC (",TEXT(D26,"0.0000%"),") and Final CD (",TEXT(J75,"0.000%"),") within tolerance, and APR difference between Final CD (",TEXT(J75,"0.000%"),") and preliminary CD (",TEXT(G50,"0.000%"),") not within tolerance."),IF(AND(D12="Fixed",D26=J75,J75>G50,J75-G50>0.125%),CONCATENATE("APR difference between Final CD (",TEXT(J75,"0.000%"),") and Prelim CD (",TEXT(G50,"0.000%"),") not within tolerance"),IF(AND(D19="W5libio225",D26<>J75,G50=J75,OR(D26-J75>0.25%,D26-J75<-0.25%)),CONCATENATE("APR difference between ELC (",TEXT(D26,"0.0000%"),") and Final CD (",TEXT(J75,"0.000%"),") not within tolerance"),IF(AND(D19="W5libio225",D26<>J75,G50=J75,OR(D26-J75<0.25%,D26-J75>0.25%)),CONCATENATE("APR difference between ELC (",TEXT(D26,"0.0000%"),") and Final CD (",TEXT(J75,"0.000%"),") not equal but within tolerance"),IF(AND(D19="W5libio225",D26<>J75,J75>G50,J75-G50>0.25%,OR(D26-J75>0.25%,D26-J75<-0.25%)),CONCATENATE("APR difference between ELC (",TEXT(D26,"0.0000%"),") and Final CD (",TEXT(J75,"0.000%"),") not within tolerance, and difference between Final CD (",TEXT(J75,"0.000%"),") and Preliminary CD (",TEXT(G50,"0.000%"),") not within tolerance"),IF(AND(D19="W5libio225",D26<>J75,J75>G50,J75-G50>0.25%,OR(D26-J75<0.25%,D26-J75>-0.25%)),CONCATENATE("APR difference between ELC (",TEXT(D26,"0.0000%"),") and Final CD (",TEXT(J75,"0.000%"),") within tolerance, and APR difference between Final CD (",TEXT(J75,"0.000%"),") and preliminary CD (",TEXT(G50,"0.000%"),") not within tolerance."),IF(AND(D19="W5libio225",D26<>J75,J75>G50,J75-G50<0.25%,OR(D26-J75>0.25%,D26-J75<-0.25%)),CONCATENATE("APR difference between ELC (",TEXT(D26,"0.0000%"),") and Final CD (",TEXT(J75,"0.000%"),") not within tolerance, and difference between Final CD (",TEXT(J75,"0.000%"),") and Preliminary CD (",TEXT(G50,"0.000%"),") not within tolerance"),IF(AND(D19="W5libio225",D26<>J75,J75>G50,J75-G50<0.25%,OR(D26-J75<0.25%,D26-J75>-0.25%)),CONCATENATE("APR difference between ELC (",TEXT(D26,"0.0000%"),") and Final CD (",TEXT(J75,"0.000%"),") within tolerance, and APR difference between Final CD (",TEXT(J75,"0.000%"),") and preliminary CD (",TEXT(G50,"0.000%"),") not within tolerance."),IF(AND(D12="W5libio225",D26=J75,J75>G50,J75-G50>0.25%),CONCATENATE("APR difference between Final CD (",TEXT(J75,"0.000%"),") and Prelim CD (",TEXT(G50,"0.000%"),") not within tolerance"),IF(AND(D19="c_p10lib225",D9="c_p1lib225",D9="c_p2lib225",D9="c_p3lib225",D9="c_p5lib225",D9="c_p7lib225",D26<>J75,G50=J75,OR(D26-J75>0.25%,D26-J75<-0.25%)),CONCATENATE("APR difference between ELC (",TEXT(D26,"0.0000%"),") and Final CD (",TEXT(J75,"0.000%"),") not within tolerance"),IF(AND(D19="c_p10lib225",D9="c_p1lib225",D9="c_p2lib225",D9="c_p3lib225",D9="c_p5lib225",D9="c_p7lib225",D26<>J75,G50=J75,OR(D26-J75<0.25%,D26-J75>0.25%)),CONCATENATE("APR difference between ELC (",TEXT(D26,"0.0000%"),") and Final CD (",TEXT(J75,"0.000%"),") not equal but within tolerance"),IF(AND(D19="c_p10lib225",D9="c_p1lib225",D9="c_p2lib225",D9="c_p3lib225",D9="c_p5lib225",D9="c_p7lib225",D26<>J75,J75>G50,J75-G50>0.25%,OR(D26-J75>0.25%,D26-J75<-0.25%)),CONCATENATE("APR difference between ELC (",TEXT(D26,"0.0000%"),") and Final CD (",TEXT(J75,"0.000%"),") not within tolerance, and difference between Final CD (",TEXT(J75,"0.000%"),") and Preliminary CD (",TEXT(G50,"0.000%"),") not within tolerance"),IF(AND(D19="c_p10lib225",D9="c_p1lib225",D9="c_p2lib225",D9="c_p3lib225",D9="c_p5lib225",D9="c_p7lib225",D26<>J75,J75>G50,J75-G50>0.25%,OR(D26-J75<0.25%,D26-J75>-0.25%)),CONCATENATE("APR difference between ELC (",TEXT(D26,"0.0000%"),") and Final CD (",TEXT(J75,"0.000%"),") within tolerance, and APR difference between Final CD (",TEXT(J75,"0.000%"),") and preliminary CD (",TEXT(G50,"0.000%"),") not within tolerance."),IF(AND(D19="c_p10lib225",D9="c_p1lib225",D9="c_p2lib225",D9="c_p3lib225",D9="c_p5lib225",D9="c_p7lib225",D26<>J75,J75>G50,J75-G50<0.25%,OR(D26-J75>0.25%,D26-J75<-0.25%)),CONCATENATE("APR difference between ELC (",TEXT(D26,"0.0000%"),") and Final CD (",TEXT(J75,"0.000%"),") not within tolerance, and difference between Final CD (",TEXT(J75,"0.000%"),") and Preliminary CD (",TEXT(G50,"0.000%"),") not within tolerance"),IF(AND(D19="c_p10lib225",D9="c_p1lib225",D9="c_p2lib225",D9="c_p3lib225",D9="c_p5lib225",D9="c_p7lib225",D26<>J75,J75>G50,J75-G50<0.25%,OR(D26-J75<0.25%,D26-J75>-0.25%)),CONCATENATE("APR difference between ELC (",TEXT(D26,"0.0000%"),") and Final CD (",TEXT(J75,"0.000%"),") within tolerance, and APR difference between Final CD (",TEXT(J75,"0.000%"),") and preliminary CD (",TEXT(G50,"0.000%"),") not within tolerance."),IF(AND(D12="c_p10lib225",D9="c_p1lib225",D9="c_p2lib225",D9="c_p3lib225",D9="c_p5lib225",D9="c_p7lib225",D26=J75,J75>G50,J75-G50>0.25%),CONCATENATE("APR difference between Final CD (",TEXT(J75,"0.000%"),") and Prelim CD (",TEXT(G50,"0.000%"),") not within tolerance"),"")))))))))))))))))))))
I have this formula in cell L60, and it seems to be working how I want it. At least by all the testing I've done so far. But I just noticed the individual cells (when I'm in the formula box) aren't color coding. Like how it normally does one green, blue, etc. Which I find convenient!
Normally when the cell is just black with no color, that means I've forgotten a comma or a parenthesis; it's a good indication something is going to fail. But as I said, I'm not getting an error or anything.
And it's not even like halfway through they stop color coding. It's from the very first cell.
Any idea why they're not color coding?