Formula Too Long Error Message

Question

I’ve used this formula:

=IF(E6="", 0,ROUND(IF(E6="F",M7*26,"")&IF(E6="F,M",(M7*14)+(L7*12),"")&IF(E6="F,M,2M",(M7*14)+(L7*6)+(K7*6),"")&IF(E6="F,M,2M,3M",(M7*14)+(L7*4)+(K7*4)+(J7*4),"")&IF(E6="F,M,2M,3M,4M",(M7*14)+(L7*4)+(K7*2)+(J7*3)+(I7*3),"")&IF(E6="F,M,2M,3M,4M,6M",(M7*14)+(L7*4)+(K7*2)+(J7*2)+(I7*2)+(H7*2),"")&IF(E6="F,M,2M,3M,4M,6M,Y",(M7*14)+(L7*4)+(K7*2)+(J7*2)+(I7*2)+H7+G7,"")&IF(E6="F,2M",(M7*20)+(K7*6),"")&IF(E6="F,2M,3M",(M7*18)+(K7*4)+(J7*4),"")&IF(E6="F,2M,3M,4M",(M7*18)+(K7*2)+(J7*3)+(I7*3),"")&IF(E6="F,2M,3M,4M,6M",(M7*18)+(K7*2)+(J7*2)+(I7*2)+(H7*2),"")&IF(E6="F,2M,3M,4M,6M,Y",(M7*18)+(K7*2)+(J7*2)+(I7*2)+H7+G7,"")&IF(E6="F,3M",(M7*22)+(J7*4),"")&IF(E6="F,3M,4M",(M7*20)+(J7*3)+(I7*3),"")&IF(E6="F,3M,4M,6M",(M7*20)+(J7*2)+(I7*2)+(H7*2),"")&IF(E6="F,3M,4M,6M,Y",(M7*20)+(J7*2)+(I7*2)+H7+G7,"")&IF(E6="F,4M",(M7*23)+(I7*3),"")&IF(E6="F,4M,6M",(M7*22)+(I7*2)+(H7*2),"")&IF(E6="F,4M,6M,Y",(M7*22)+(I7*2)+H7+G7,"")&IF(E6="F,6M",(M7*24)+(H7*2),"")&IF(E6="F,6M,Y",(M7*24)+H7+G7,""),2))

When I add another &IF I get an error message saying the formula is too long.  Is there a way round this?  I’m only halfway through the possible text combinations.  I don’t know about macros or VBA but would be happy to check it out if that a solution and you can point me in the right direction.

Thanks

Ron

Answer 1

The file is called Test.xls and can be accessed via this link

http://sdrv.ms/13j0CAg

Thanks

Ron

Answer 2

Hi,

I cannot visualize your question - upload your file to SkyDrive and share the link of the uploaded file here.

Answer 3

jay,

I receive an Excel sheet with a text string in E6, E8, E10 etc up to 1000 lines.  The string can be F, M, 2M, 3M, 4M, 6M, Y, or any combination of them.   Columns G to M have titles for each individual string.  The formula sits in F7.

If the string is “F”, cell M7 highlights to indicate that a cost needs to be entered.  The formula in F7 will check E6 to determine what the string is and then apply the relevant arithmetical formula, in this case M7*26.  The answer appears in F7.

This all works OK.  The problem is that there are a large number of possible combinations of strings therefore I need a large number of &IF statements and this takes the formula over the Excel size limit and gives me the “Formula Too Long” error.

I’m thinking that there must be a way of creating a macro or VBA that will allow all the possible combinations of strings but I don’t have the expertise to create it.  Any help you can provide would be appreciated.

Answer 4

What exactly you want to achieve, can you describe your conditions separately so that we provide you a short and flexible formula.

Answer 5

I forgot to say that I need to copy this down a column so a vlookup will not work.

Ron