Uses more than 64 levels of nesting

[mohammaduir]

Hi

Can you please help me shorten this formula so excel can accept it without giving an error?

a message appear tells me that:

(" The specified formula can not be entered because it uses more than 64 levels of nesting").

Thanks for your kind cooperation.

=IF(AND([@74]<>"",OR([@74]>=3.5*[@73],[@74]>=3.5*[@72])),1,
IF(AND([@73]<>"",OR([@73]>=3.5*[@72],[@73]>=3.5*[@71])),1,
IF(AND([@72]<>"",OR([@72]>=3.5*[@71],[@72]>=3.5*[@70])),1,
IF(AND([@71]<>"",OR([@71]>=3.5*[@70],[@71]>=3.5*[@69])),1,
IF(AND([@70]<>"",OR([@70]>=3.5*[@69],[@70]>=3.5*[@68])),1,
IF(AND([@69]<>"",OR([@69]>=3.5*[@68],[@69]>=3.5*[@67])),1,
IF(AND([@68]<>"",OR([@68]>=3.5*[@67],[@68]>=3.5*[@66])),1,
IF(AND([@67]<>"",OR([@67]>=3.5*[@66],[@67]>=3.5*[@65])),1,
IF(AND([@66]<>"",OR([@66]>=3.5*[@65],[@66]>=3.5*[@64])),1,
IF(AND([@65]<>"",OR([@65]>=3.5*[@64],[@65]>=3.5*[@63])),1,
IF(AND([@64]<>"",OR([@64]>=3.5*[@63],[@64]>=3.5*[@62])),1,
IF(AND([@63]<>"",OR([@63]>=3.5*[@62],[@63]>=3.5*[@61])),1,
IF(AND([@62]<>"",OR([@62]>=3.5*[@61],[@62]>=3.5*[@60])),1,
IF(AND([@61]<>"",OR([@61]>=3.5*[@60],[@61]>=3.5*[@59])),1,
IF(AND([@60]<>"",OR([@60]>=3.5*[@59],[@60]>=3.5*[@58])),1,
IF(AND([@59]<>"",OR([@59]>=3.5*[@58],[@59]>=3.5*[@57])),1,
IF(AND([@58]<>"",OR([@58]>=3.5*[@57],[@58]>=3.5*[@56])),1,
IF(AND([@57]<>"",OR([@57]>=3.5*[@56],[@57]>=3.5*[@55])),1,
IF(AND([@56]<>"",OR([@56]>=3.5*[@55],[@56]>=3.5*[@54])),1,
IF(AND([@55]<>"",OR([@55]>=3.5*[@54],[@55]>=3.5*[@53])),1,
IF(AND([@54]<>"",OR([@54]>=3.5*[@53],[@54]>=3.5*[@52])),1,
IF(AND([@53]<>"",OR([@53]>=3.5*[@52],[@53]>=3.5*[@51])),1,
IF(AND([@52]<>"",OR([@52]>=3.5*[@51],[@52]>=3.5*[@50])),1,
IF(AND([@51]<>"",OR([@51]>=3.5*[@50],[@51]>=3.5*[@49])),1,
IF(AND([@50]<>"",OR([@50]>=3.5*[@49],[@50]>=3.5*[@49])),1,
IF(AND([@49]<>"",OR([@49]>=3.5*[@48],[@49]>=3.5*[@47])),1,
IF(AND([@48]<>"",OR([@48]>=3.5*[@47],[@48]>=3.5*[@46])),1,
IF(AND([@47]<>"",OR([@47]>=3.5*[@46],[@47]>=3.5*[@45])),1,
IF(AND([@46]<>"",OR([@46]>=3.5*[@45],[@46]>=3.5*[@44])),1,
IF(AND([@45]<>"",OR([@45]>=3.5*[@44],[@45]>=3.5*[@43])),1,
IF(AND([@44]<>"",OR([@44]>=3.5*[@43],[@44]>=3.5*[@42])),1,
IF(AND([@43]<>"",OR([@43]>=3.5*[@42],[@43]>=3.5*[@41])),1,
IF(AND([@42]<>"",OR([@42]>=3.5*[@41],[@42]>=3.5*[@40])),1,
IF(AND([@41]<>"",OR([@41]>=3.5*[@40],[@41]>=3.5*[@39])),1,
IF(AND([@40]<>"",OR([@40]>=3.5*[@39],[@40]>=3.5*[@38])),1,
IF(AND([@39]<>"",OR([@39]>=3.5*[@38],[@39]>=3.5*[@37])),1,
IF(AND([@38]<>"",OR([@38]>=3.5*[@37],[@38]>=3.5*[@36])),1,
IF(AND([@37]<>"",OR([@37]>=3.5*[@36],[@37]>=3.5*[@35])),1,
IF(AND([@36]<>"",OR([@36]>=3.5*[@35],[@36]>=3.5*[@34])),1,
IF(AND([@35]<>"",OR([@35]>=3.5*[@34],[@35]>=3.5*[@33])),1,
IF(AND([@34]<>"",OR([@34]>=3.5*[@33],[@34]>=3.5*[@32])),1,
IF(AND([@33]<>"",OR([@33]>=3.5*[@32],[@33]>=3.5*[@31])),1,
IF(AND([@32]<>"",OR([@32]>=3.5*[@31],[@32]>=3.5*[@30])),1,
IF(AND([@31]<>"",OR([@31]>=3.5*[@30],[@31]>=3.5*[@29])),1,
IF(AND([@30]<>"",OR([@30]>=3.5*[@29],[@30]>=3.5*[@28])),1,
IF(AND([@29]<>"",OR([@29]>=3.5*[@28],[@29]>=3.5*[@27])),1,
IF(AND([@28]<>"",OR([@28]>=3.5*[@27],[@28]>=3.5*[@26])),1,
IF(AND([@27]<>"",OR([@27]>=3.5*[@26],[@27]>=3.5*[@25])),1,
IF(AND([@26]<>"",OR([@26]>=3.5*[@25],[@26]>=3.5*[@24])),1,
IF(AND([@25]<>"",OR([@25]>=3.5*[@24],[@25]>=3.5*[@23])),1,
IF(AND([@24]<>"",OR([@24]>=3.5*[@23],[@24]>=3.5*[@22])),1,
IF(AND([@23]<>"",OR([@23]>=3.5*[@22],[@23]>=3.5*[@21])),1,
IF(AND([@22]<>"",OR([@22]>=3.5*[@21],[@22]>=3.5*[@20])),1,
IF(AND([@21]<>"",OR([@21]>=3.5*[@20],[@21]>=3.5*[@19])),1,
IF(AND([@20]<>"",OR([@20]>=3.5*[@19],[@20]>=3.5*[@18])),1,
IF(AND([@19]<>"",OR([@19]>=3.5*[@18],[@19]>=3.5*[@17])),1,
IF(AND([@18]<>"",OR([@18]>=3.5*[@17],[@18]>=3.5*[@16])),1,
IF(AND([@17]<>"",OR([@17]>=3.5*[@16],[@17]>=3.5*[@15])),1,
IF(AND([@16]<>"",OR([@16]>=3.5*[@15],[@16]>=3.5*[@14])),1,
IF(AND([@15]<>"",OR([@15]>=3.5*[@14],[@15]>=3.5*[@13])),1,
IF(AND([@14]<>"",OR([@14]>=3.5*[@13],[@14]>=3.5*[@12])),1,
IF(AND([@13]<>"",OR([@13]>=3.5*[@12],[@13]>=3.5*[@11])),1,
IF(AND([@12]<>"",OR([@12]>=3.5*[@11],[@12]>=3.5*[@10])),1,
IF(AND([@11]<>"",OR([@11]>=3.5*[@10],[@11]>=3.5*[@9])),1,
IF(AND([@10]<>"",OR([@10]>=3.5*[@9],[@10]>=3.5*[@8])),1,
IF(AND([@9]<>"",OR([@9]>=3.5*[@8],[@9]>=3.5*[@7])),1,
IF(AND([@8]<>"",OR([@8]>=3.5*[@7],[@8]>=3.5*[@6])),1,
IF(AND([@7]<>"",OR([@7]>=3.5*[@6],[@7]>=3.5*[@5])),1,
IF(AND([@6]<>"",OR([@6]>=3.5*[@5],[@6]>=3.5*[@4])),1,
IF(AND([@5]<>"",OR([@5]>=3.5*[@4],[@5]>=3.5*[@3])),1,
IF(AND([@4]<>"",OR([@4]>=3.5*[@3],[@4]>=3.5*[@2])),1,
IF(AND([@3]<>"",OR([@3]>=3.5*[@2],[@3]>=3.5*[@1])),1,
IF(AND([@2]<>"",OR([@2]>=3.5*[@1],[@2]>=3.5*[@0])),1,
IF(AND([@1]<>"",([@1]>=3.5*[@0])),1,0))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

 

[maabadi]

Divide this formula to helper columns and use it at the original formula.
First Helper (At Cell A2):

=IF(AND([@37]<>"",OR([@37]>=3.5*[@36],[@37]>=3.5*[@35])),1,IF(AND([@36]<>"",OR([@36]>=3.5*[@35],[@36]>=3.5*[@34])),1,IF(AND([@35]<>"",OR([@35]>=3.5*[@34],[@35]>=3.5*[@33])),1,IF(AND([@34]<>"",OR([@34]>=3.5*[@33],[@34]>=3.5*[@32])),1,IF(AND([@33]<>"",OR([@33]>=3.5*[@32],[@33]>=3.5*[@31])),1,IF(AND([@32]<>"",OR([@32]>=3.5*[@31],[@32]>=3.5*[@30])),1,IF(AND([@31]<>"",OR([@31]>=3.5*[@30],[@31]>=3.5*[@29])),1,IF(AND([@30]<>"",OR([@30]>=3.5*[@29],[@30]>=3.5*[@28])),1,
IF(AND([@29]<>"",OR([@29]>=3.5*[@28],[@29]>=3.5*[@27])),1,IF(AND([@28]<>"",OR([@28]>=3.5*[@27],[@28]>=3.5*[@26])),1,IF(AND([@27]<>"",OR([@27]>=3.5*[@26],[@27]>=3.5*[@25])),1,IF(AND([@26]<>"",OR([@26]>=3.5*[@25],[@26]>=3.5*[@24])),1,IF(AND([@25]<>"",OR([@25]>=3.5*[@24],[@25]>=3.5*[@23])),1,IF(AND([@24]<>"",OR([@24]>=3.5*[@23],[@24]>=3.5*[@22])),1,IF(AND([@23]<>"",OR([@23]>=3.5*[@22],[@23]>=3.5*[@21])),1,IF(AND([@22]<>"",OR([@22]>=3.5*[@21],[@22]>=3.5*[@20])),1,
IF(AND([@21]<>"",OR([@21]>=3.5*[@20],[@21]>=3.5*[@19])),1,IF(AND([@20]<>"",OR([@20]>=3.5*[@19],[@20]>=3.5*[@18])),1,IF(AND([@19]<>"",OR([@19]>=3.5*[@18],[@19]>=3.5*[@17])),1,IF(AND([@18]<>"",OR([@18]>=3.5*[@17],[@18]>=3.5*[@16])),1,IF(AND([@17]<>"",OR([@17]>=3.5*[@16],[@17]>=3.5*[@15])),1,IF(AND([@16]<>"",OR([@16]>=3.5*[@15],[@16]>=3.5*[@14])),1,IF(AND([@15]<>"",OR([@15]>=3.5*[@14],[@15]>=3.5*[@13])),1,IF(AND([@14]<>"",OR([@14]>=3.5*[@13],[@14]>=3.5*[@12])),1,
IF(AND([@13]<>"",OR([@13]>=3.5*[@12],[@13]>=3.5*[@11])),1,IF(AND([@12]<>"",OR([@12]>=3.5*[@11],[@12]>=3.5*[@10])),1,IF(AND([@11]<>"",OR([@11]>=3.5*[@10],[@11]>=3.5*[@9])),1,IF(AND([@10]<>"",OR([@10]>=3.5*[@9],[@10]>=3.5*[@8])),1,IF(AND([@9]<>"",OR([@9]>=3.5*[@8],[@9]>=3.5*[@7])),1,IF(AND([@8]<>"",OR([@8]>=3.5*[@7],[@8]>=3.5*[@6])),1,IF(AND([@7]<>"",OR([@7]>=3.5*[@6],[@7]>=3.5*[@5])),1,IF(AND([@6]<>"",OR([@6]>=3.5*[@5],[@6]>=3.5*[@4])),1,IF(AND([@5]<>"",OR([@5]>=3.5*[@4],[@5]>=3.5*[@3])),1,IF(AND([@4]<>"",OR([@4]>=3.5*[@3],[@4]>=3.5*[@2])),1,IF(AND([@3]<>"",OR([@3]>=3.5*[@2],[@3]>=3.5*[@1])),1,IF(AND([@2]<>"",OR([@2]>=3.5*[@1],[@2]>=3.5*[@0])),1,IF(AND([@1]<>"",([@1]>=3.5*[@0])),1,0)))))))))))))))))))))))))))))))))))))

=IF(AND([@74]<>"",OR([@74]>=3.5*[@73],[@74]>=3.5*[@72])),1,IF(AND([@73]<>"",OR([@73]>=3.5*[@72],[@73]>=3.5*[@71])),1,IF(AND([@72]<>"",OR([@72]>=3.5*[@71],[@72]>=3.5*[@70])),1,IF(AND([@71]<>"",OR([@71]>=3.5*[@70],[@71]>=3.5*[@69])),1,IF(AND([@70]<>"",OR([@70]>=3.5*[@69],[@70]>=3.5*[@68])),1,IF(AND([@69]<>"",OR([@69]>=3.5*[@68],[@69]>=3.5*[@67])),1,IF(AND([@68]<>"",OR([@68]>=3.5*[@67],[@68]>=3.5*[@66])),1,IF(AND([@67]<>"",OR([@67]>=3.5*[@66],[@67]>=3.5*[@65])),1,IF(AND([@66]<>"",OR([@66]>=3.5*[@65],[@66]>=3.5*[@64])),1,IF(AND([@65]<>"",OR([@65]>=3.5*[@64],[@65]>=3.5*[@63])),1,IF(AND([@64]<>"",OR([@64]>=3.5*[@63],[@64]>=3.5*[@62])),1,IF(AND([@63]<>"",OR([@63]>=3.5*[@62],[@63]>=3.5*[@61])),1,IF(AND([@62]<>"",OR([@62]>=3.5*[@61],[@62]>=3.5*[@60])),1,IF(AND([@61]<>"",OR([@61]>=3.5*[@60],[@61]>=3.5*[@59])),1,IF(AND([@60]<>"",OR([@60]>=3.5*[@59],[@60]>=3.5*[@58])),1,IF(AND([@59]<>"",OR([@59]>=3.5*[@58],[@59]>=3.5*[@57])),1,IF(AND([@58]<>"",OR([@58]>=3.5*[@57],[@58]>=3.5*[@56])),1,IF(AND([@57]<>"",OR([@57]>=3.5*[@56],[@57]>=3.5*[@55])),1,IF(AND([@56]<>"",OR([@56]>=3.5*[@55],[@56]>=3.5*[@54])),1,IF(AND([@55]<>"",OR([@55]>=3.5*[@54],[@55]>=3.5*[@53])),1,IF(AND([@54]<>"",OR([@54]>=3.5*[@53],[@54]>=3.5*[@52])),1,IF(AND([@53]<>"",OR([@53]>=3.5*[@52],[@53]>=3.5*[@51])),1,IF(AND([@52]<>"",OR([@52]>=3.5*[@51],[@52]>=3.5*[@50])),1,IF(AND([@51]<>"",OR([@51]>=3.5*[@50],[@51]>=3.5*[@49])),1,
IF(AND([@50]<>"",OR([@50]>=3.5*[@49],[@50]>=3.5*[@49])),1,IF(AND([@49]<>"",OR([@49]>=3.5*[@48],[@49]>=3.5*[@47])),1,IF(AND([@48]<>"",OR([@48]>=3.5*[@47],[@48]>=3.5*[@46])),1,IF(AND([@47]<>"",OR([@47]>=3.5*[@46],[@47]>=3.5*[@45])),1,IF(AND([@46]<>"",OR([@46]>=3.5*[@45],[@46]>=3.5*[@44])),1,IF(AND([@45]<>"",OR([@45]>=3.5*[@44],[@45]>=3.5*[@43])),1,IF(AND([@44]<>"",OR([@44]>=3.5*[@43],[@44]>=3.5*[@42])),1,IF(AND([@43]<>"",OR([@43]>=3.5*[@42],[@43]>=3.5*[@41])),1,
IF(AND([@42]<>"",OR([@42]>=3.5*[@41],[@42]>=3.5*[@40])),1,IF(AND([@41]<>"",OR([@41]>=3.5*[@40],[@41]>=3.5*[@39])),1,IF(AND([@40]<>"",OR([@40]>=3.5*[@39],[@40]>=3.5*[@38])),1,IF(AND([@39]<>"",OR([@39]>=3.5*[@38],[@39]>=3.5*[@37])),1,IF(AND([@38]<>"",OR([@38]>=3.5*[@37],[@38]>=3.5*[@36])),1,A2)))))))))))))))))))))))))))))))))))))

 

[jasonb75]

Just noticed this old question in the 'Similar threads' at the bottom of another thread I was responding to.

A formula with no information to back it up gives us little to work with, but theoretically you should be able to cut that formula down to something like this.

=--((SUMPRODUCT(([[@74]:[@2]]<>"")*(([[@74]:[@2]]>=3.5*[[@73]:[@1]])+([[@74]:[@2]]>=3.5*[[@72]:[@0]])))+AND([@1]<>"",[@1]>=3.5*[@0]))>0)