Formula error "The formula you typed contains an error"

Question

Hi all,

I have the below formula that returns me an error "The formula you typed contains an error". Is there any way to resolve this? Thanks.

=IF(OR(CW8>0),CONCATENATE(IF(CU8=0,0,IF(CU8<91,1,IF(AND(CU8>=91,CU8<=180),2,IF(AND(CU8>=180,CU8<=365),3,IF(AND(CU8>365,CU8<=730),4,IF(AND(CU8>365,CU8<=730),5,6)))))),"R"),IF(CU8=0,0,IF(CU8<91,1,IF(AND(CU8>=91,CU8<=180),2,IF(AND(CU8>=180,CU8<=365),3,IF(AND(CU8>365,CU8<=730),4,5))))))

Answer 1

1.  Purchase Excel 2007 (or later) to get expand the function nesting limit which, in Excel 2003, is seven.

2.  You have some clear redundancies in your formula

At the beginning:   ... OR(CW8>0) ...  The OR is not doing anything at all, and should be eliminated.

For example, you have a bunch of "AND" functions which are completely unneccesary.

... IF(CU8<91,1,IF(AND(CU8>=91,CU8<=180),2,IF(AND(CU8>=180,CU8<=365) ...

is the same as ...IF(CU8<91,1,IF(CU8<=180,2,IF(CU8<=365,...

since CU8 is being tested in sequence, and each failed test excludes that numeric range.

It may be possible to replace the entire IF sequence with regard to CU8 with a lookup formula:

3.  You have some overlapping ranges making it not possible to figure out exactly what you want to do, but the following should come close, although you may have to adjust the arrays a bit.

4.  If you require more help, please post exactly what you are trying to do.  But this formula should help:

=VLOOKUP(CU8,{0,0;1,1;91,2;180,3;365,4;730,5},2) & IF(CW8>0,"R","")

Answer 2

Dear HuiliHuang,

Try this, may help you. I wrtore this formula as per my understanding.

As per my understanding you requirement is

if CW8 is not blank then

if CU8 is <=0, OR

if CU8 is <90, 1R and goes on and max is 5R (i.e 0R, 1R, 2R, 3R, 4R & 5R)

and if CW8 is blank then your result should be as per above excluding R i.e 0,1,2,3,4 & 5

=IF(CW8>0,IF(CU8<=0,0,IF(CU8<91,1,IF(CU8<180,2,IF(CU8<365,3,IF(CU8<730,4,5)))))&"R",IF(CU8<=0,0,IF(CU8<91,1,IF(CU8<180,2,IF(CU8<365,3,IF(CU8<730,4,5))))))

or

=IF(ISBLANK(CW8),IF(CU8<=0,0,IF(CU8<91,1,IF(CU8<180,2,IF(CU8<365,3,IF(CU8<730,4,5))))),IF(CU8<=0,0,IF(CU8<91,1,IF(CU8<180,2,IF(CU8<365,3,IF(CU8<730,4,5)))))&"R")

Thankyou

A R Vital

Answer 3

HuiliHuang wrote:

I have the below formula that returns me an error "The formula you typed contains an error". Is there any way to resolve this? Thanks.

=IF(OR(CW8>0),CONCATENATE(IF(CU8=0,0,IF(CU8<91,1,IF(AND(CU8>=91,CU8<=180),2,

IF(AND(CU8>=180,CU8<=365),3,IF(AND(CU8>365,CU8<=730),4,

IF(AND(CU8>365,CU8<=730),5,6)))))),"R"),

IF(CU8=0,0,IF(CU8<91,1,IF(AND(CU8>=91,CU8<=180),2,IF(AND(CU8>=180,CU8<=365),3,

IF(AND(CU8>365,CU8<=730),4,5))))))

I am almost sorry to say that you can write that formula exactly as you intended (making some assumptions; see below) if you eliminate the unnecessary conditions.  You could write:

=IF(CW8>0,IF(CU8=0,0,IF(CU8<91,1,IF(CU8<=180,2,IF(CU8<=365,3,IF(CU8<=730,4,5))))) & "R",

IF(CU8=0,0,IF(CU8<91,1,IF(CU8<=180,2,IF(CU8<=365,3,IF(CU8<=730,4,5))))))

Or somewhat better:

=IF(CU8=0,0,IF(CU8<91,1,IF(CU8<=180,2,IF(CU8<=365,3,IF(CU8<=730,4,5))))) & IF(CW8>0,"R","")

But note that the second formula is not exactly the same as the first formula.  When CW8<=0, the first formula returns numeric 0 through 5, whereas the second formula returns text "0" through "5".

IMHO, that difference is a "good thing".  I think it is better if the formula consistently returns text, since it must return text when "R" is appended.  But that is really your decision to make.

Alternatively, you might be able to use the following formula, which is more concise as well as more efficient and easily extended if the need arises in the future:

=LOOKUP(CU8,{0,1,91,181,365,730},{0,1,2,3,4,5}) & IF(CW8>0,"R","")

However, that makes the additional assumption that CU8 is never negative.  If could be negative, your original formula would return 5 or "5R", the same value that it returns when CU8>730.  That seems odd, IMHO.  But again, that is your decision to make.

Also, the LOOKUP formula might not behave exactly the same as your original formula if CU8 might be non-integer.  For example, the LOOKUP formula returns 0 if CU8 is 0.9, whereas your original formula returns 1.

Does that matter?  If it does, we might be able to fix the LOOKUP formula (a good idea) if you tell us the number of decimal places that you expect the value in CU8 to be accurate to.

Finally, note that your original formula contains a number of ambiguities which I resolved my making some additional assumptions.  If my assumptions are incorrect, please let us know how we should resolve them.

1. You wrote:  IF(AND(CU8>=91,CU8<=180),2,IF(AND(CU8>=180,CU8<=365),3.  When CU8=180, it is unclear whether you want 2 or 3.

Since you wrote CU8>365 in the next term, I assume you meant CU8>180 here.

2. You wrote:  IF(AND(CU8>365,CU8<=730),4,IF(AND(CU8>365,CU8<=730),5,6.  Note that the conditions are the same in both parts.  So when CU8>365 and CU8<=730, it is unclear whether you want 4 or 5.

Since you wrote IF(AND(CU8>365,CU8<=730),4,5 in the part that applies when CW8<=0, I assume that is what you really meant in the part the first part.

However, if you truly mean to return 6 when CU8>730 and CW8>0 and only 5 when CU8>730 and CW8<=0, please let us know.