How many levels can be nested?


P

Peter Bailey

I have 14 columns and have created the following formula that works up to the if(l12) and I cannot see the error so i am assuming that you can only nest so many levels? I know the logic works as for lesser nesting it works.

Regards in advance
Peter

=IF(S12<>"",COUNTIF(F12:S12,">1"),

IF(R12<>"",COUNTIF(F12:S12,">1"),

IF(Q12<>"",COUNTIF(F12:S12,">1"),

IF(P12<>"",COUNTIF(F12:S12,">1"),

IF(O12<>"",COUNTIF(F12:S12,">1"),

IF(N12<>"",COUNTIF(F12:S12,">1"),

IF(M12<>"",COUNTIF(F12:S12,">1"),

IF(L12<>"",COUNTIF(F12:S12,">1"), shows error here

IF(K12<>"",COUNTIF(F12:S12,">1"),

IF(J12<>"",COUNTIF(F12:S12,">1"),

IF(I12<>"",COUNTIF(F12:S12,">1"),

IF(H12<>"",COUNTIF(F12:S12,">1"),

IF(G12<>"",COUNTIF(F12:S12,">1"),

IF(F12<>"",COUNTIF(F12:S12,">1"),""))))))))))))))
 
Ad

Advertisements

F

Frank Kabel

Hi
the maximum level is 7 nested function calls. But in your case why not
try:
=IF(COUNTA(F12:S12),COUNTIF(F12:S12,">1"),"")

--
Regards
Frank Kabel
Frankfurt, Germany

Newsbeitrag I have 14 columns and have created the following formula that works up
to the if(l12) and I cannot see the error so i am assuming that you can
only nest so many levels? I know the logic works as for lesser nesting
it works.

Regards in advance
Peter

=IF(S12<>"",COUNTIF(F12:S12,">1"),

IF(R12<>"",COUNTIF(F12:S12,">1"),

IF(Q12<>"",COUNTIF(F12:S12,">1"),

IF(P12<>"",COUNTIF(F12:S12,">1"),

IF(O12<>"",COUNTIF(F12:S12,">1"),

IF(N12<>"",COUNTIF(F12:S12,">1"),

IF(M12<>"",COUNTIF(F12:S12,">1"),

IF(L12<>"",COUNTIF(F12:S12,">1"), shows error here

IF(K12<>"",COUNTIF(F12:S12,">1"),

IF(J12<>"",COUNTIF(F12:S12,">1"),

IF(I12<>"",COUNTIF(F12:S12,">1"),

IF(H12<>"",COUNTIF(F12:S12,">1"),

IF(G12<>"",COUNTIF(F12:S12,">1"),

IF(F12<>"",COUNTIF(F12:S12,">1"),""))))))))))))))
 
H

Harald Staff

Hi Peter

7 is the maximum of nested IFs. But you seem to want the same operation no matter which cell that contain something. Here are two alternative ways to check that:

=IF(F12&G12&H12&I12&J12&K12&L12&M12<>"","something","empty")
=IF(SUMPRODUCT(LEN(F12:Z12))>0,"something","empty")

HTH. Best wishes Harald
"Peter Bailey" <[email protected]> skrev i melding I have 14 columns and have created the following formula that works up to the if(l12) and I cannot see the error so i am assuming that you can only nest so many levels? I know the logic works as for lesser nesting it works.

Regards in advance
Peter

=IF(S12<>"",COUNTIF(F12:S12,">1"),

IF(R12<>"",COUNTIF(F12:S12,">1"),

IF(Q12<>"",COUNTIF(F12:S12,">1"),

IF(P12<>"",COUNTIF(F12:S12,">1"),

IF(O12<>"",COUNTIF(F12:S12,">1"),

IF(N12<>"",COUNTIF(F12:S12,">1"),

IF(M12<>"",COUNTIF(F12:S12,">1"),

IF(L12<>"",COUNTIF(F12:S12,">1"), shows error here

IF(K12<>"",COUNTIF(F12:S12,">1"),

IF(J12<>"",COUNTIF(F12:S12,">1"),

IF(I12<>"",COUNTIF(F12:S12,">1"),

IF(H12<>"",COUNTIF(F12:S12,">1"),

IF(G12<>"",COUNTIF(F12:S12,">1"),

IF(F12<>"",COUNTIF(F12:S12,">1"),""))))))))))))))
 
P

Peter Bailey

maybe I should give a little background

we have

learner ref in a cell with centre error rates (a value > 1 shows a particular error code) data in 14 columns to the right:

124556 fred jones 1 2 1 1 5 3 ... then a final cell called error cout ie in this case 3

the user can enter error rate data in any column they want to start with first (each column represents a certain activity that might have an error.

I wanted to show an error rate in the error count cell of zero (if no errors found in any associated column) but also if no learner was entered or learner entered but errors not put in yet that the cell would then be blank otherwise I end up with cells that say 0.

Thats why I didt go for f12 & G12 <> "" approach etc as I wanted it to give accumulative error counts as the user was filling in the form.

I will try your sumproduct solution as I am not familiar with that formula.

You may be able to assist with one other final part of this. On another workshhet is a list of the errorcodes with the column atitle adjacent.:

column desc error code action to take
"ILA" 1 "No action"
"ILA" 2 "get centre to fill in ILA remission section"

"Access" 1 "No action"
"Access" 2 "Ensure learners log off correctly"


and so on.

At the bottom of the form is a section called "action" and I was hoping to be able to get access to look at the learner and if there were errors lookup on the error code list and put the actions necessary in the action section.

because each learner can have x errors I was not sure how to get it to fill in the actions. A lookup would find the error in the list but wouldnt know how many rows down to go in the action area ie the next avalable row.

I do know of course that for each learner and their error count that I need that number of rows to fill in the data. I assume I need to create a vba macro and get it to dynamically add the actions to the action section. Unless of course I am missing the plot and there exists a better way.

Regards in advance.

Peter


Hi Peter

7 is the maximum of nested IFs. But you seem to want the same operation no matter which cell that contain something. Here are two alternative ways to check that:

=IF(F12&G12&H12&I12&J12&K12&L12&M12<>"","something","empty")
=IF(SUMPRODUCT(LEN(F12:Z12))>0,"something","empty")

HTH. Best wishes Harald
"Peter Bailey" <[email protected]> skrev i melding I have 14 columns and have created the following formula that works up to the if(l12) and I cannot see the error so i am assuming that you can only nest so many levels? I know the logic works as for lesser nesting it works.

Regards in advance
Peter

=IF(S12<>"",COUNTIF(F12:S12,">1"),

IF(R12<>"",COUNTIF(F12:S12,">1"),

IF(Q12<>"",COUNTIF(F12:S12,">1"),

IF(P12<>"",COUNTIF(F12:S12,">1"),

IF(O12<>"",COUNTIF(F12:S12,">1"),

IF(N12<>"",COUNTIF(F12:S12,">1"),

IF(M12<>"",COUNTIF(F12:S12,">1"),

IF(L12<>"",COUNTIF(F12:S12,">1"), shows error here

IF(K12<>"",COUNTIF(F12:S12,">1"),

IF(J12<>"",COUNTIF(F12:S12,">1"),

IF(I12<>"",COUNTIF(F12:S12,">1"),

IF(H12<>"",COUNTIF(F12:S12,">1"),

IF(G12<>"",COUNTIF(F12:S12,">1"),

IF(F12<>"",COUNTIF(F12:S12,">1"),""))))))))))))))
 
Ad

Advertisements

P

Peter Bailey

Just to say Franks solution worked a treat also:

=IF(COUNTA(F12:S12),COUNTIF(F12:S12,">1"),"")
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top