Formula error

[mukeshbaviskar]

Dear friends,
In cell ‘BE8’ I want to insert this formula but it is giving error ‘missing parentheses. It’s a result sheet and the terms are as follows:

If the student is fail in 1 subject (<35) then the remark should be ‘F1’
If the student is fail in 2 subject (<35) then the remark should be ‘F2’
If the student is fail in 3 subject (<35) then the remark should be ‘F3’
If the student is fail in 4 subject (<35) then the remark should be ‘F4’
If the student is fail in 5 subject (<35) then the remark should be ‘F5’
If the student is fail in 6 subject (<35) then the remark should be ‘F6’

The formula is:

Please Login or Register  to view this content.

Please suggest me a correction.

Thanking you.

Sincerely,

Mukesh

[wcymiss]

Hi,
Maybe you can use this formula:

Please Login or Register  to view this content.

[etaf]

try
="F"&COUNTIF(J8,"<35")+COUNTIF(N8,"<35")+COUNTIF(R8,"<35")+COUNTIF(V8,"<35")+COUNTIF(Z8,"<35")+COUNTIF(AD8,"<35")

[mukeshbaviskar]

Hi etaf,
Thank you for formula. It's working fine but only one correction is required. If the student is passed in all six subjects then the remark should be 'P all'. Now it's giving 'F0' which I don't want.

Thank you.

Sincerely,

Mukesh

[mukeshbaviskar]

Hi wcymiss,
Thank you. The formula is working fine but if the student is passed in all six subjects then the remark should be 'P all'.

Regards,

Mukesh

[etaf]

=IF( (COUNTIF(J8,">=35")+COUNTIF(N8,">=35")+COUNTIF(R8,">=35")+COUNTIF(V8,">=35")+COUNTIF(Z8,">=35")+COUNTIF(AD8,">=35"))=6, "P All", "F"&COUNTIF(J8,"<35")+COUNTIF(N8,"<35")+COUNTIF(R8,"<35")+COUNTIF(V8,"<35")+COUNTIF(Z8,"<35")+COUNTIF(AD8,"<35"))

OR

IF( (COUNTIF(J8,"<35")+COUNTIF(N8,"<35")+COUNTIF(R8,"<35")+COUNTIF(V8,"<35")+COUNTIF(Z8,"<35")+COUNTIF(AD8,"<35"))=0, "P All", "F"&COUNTIF(J8,"<35")+COUNTIF(N8,"<35")+COUNTIF(R8,"<35")+COUNTIF(V8,"<35")+COUNTIF(Z8,"<35")+COUNTIF(AD8,"<35"))

[martindwilson]

=IF(SUMPRODUCT((J8:AD8<35)*(MOD(COLUMN(J8:AD8),4)=2)),"F"&SUMPRODUCT((J8:AD8<35)*(MOD(COLUMN(J8:AD8),4)=2)),"p all")
or maybe
=CHOOSE(1+SUMPRODUCT((J8:AD8<35)*(MOD(COLUMN(J8:AD8),4)=2)),"p all","f1","f2","f3","f4","f5","f6")

[mukeshbaviskar]

Hi etaf,
Ok! it's working fine. I need a final revision as follows:

If the cells are blank then the remark column should be blank. Now it shows 'F0' which I don't want.

Thanking you.

Regards,

Mukesh

[mukeshbaviskar]

Hi martindwilson,
Thank you.

Regards,

Mukesh

[etaf]

does martindwilson version also not work for blank cells

its going to get quite a lengthy formula to avoid blanks in 2003
is that version of excel you are using ?

this condition of F0 would be if ALL cells where blank

=IF( AND(J8="", N8="",R8="",V8="",Z8="",AD8="" ), "",IF( (COUNTIF(J8,">=35")+COUNTIF(N8,">=35")+COUNTIF(R8,">=35")+COUNTIF(V8,">=35")+COUNTIF(Z8,">=35")+COUNTIF(AD8,">=35"))=6, "P All", "F"&COUNTIF(J8,"<35")+COUNTIF(N8,"<35")+COUNTIF(R8,"<35")+COUNTIF(V8,"<35")+COUNTIF(Z8,"<35")+COUNTIF(AD8,"<35")))

[martindwilson]

=IF(SUMPRODUCT((J8:AD8="")*(MOD(COLUMN(J8:AD8),4)=2))=6,"",CHOOSE(1+SUMPRODUCT((J8:AD8<35)*(MOD(COLUMN(J8:AD8),4)=2)),"p all","f1","f2","f3","f4","f5","f6"))

[mukeshbaviskar]

Hi Etaf and martindwilson,
Thank you for guidance. Etaf's formula is working fine as per my requirement but it's very lengthy. No problem it's giving correct output in m. s. office 2007.

martindwilson's formula is not giving correct output as per requirement. It shows '# value' in the cell. It's shorter but not giving correct output. martindwilson please remove the error in your formula because I like it and it's shorter.

Thank you to both of you for taking trouble and sparing your valuable time to solve my problem.

Sincerely,

Mukesh

[martindwilson]

works fine for me see attached

[wcymiss]

Hi etaf,
Thank you for formula. It's working fine but only one correction is required. If the student is passed in all six subjects then the remark should be 'P all'. Now it's giving 'F0' which I don't want.

Thank you.

Sincerely,

Mukesh

Hi,

Please Login or Register  to view this content.

[martindwilson]

that still gives f6 for all blanks not blank ,the op added an extra requirement "If the cells are blank then the remark column should be blank."
so maybe
=IF(SUMPRODUCT((J8:AD8="")*(MOD(COLUMN(J8:AD8),4)=2))=6,"",SUBSTITUTE("F"&SUMPRODUCT((J8:AD8<35)*(MOD(COLUMN(J8:AD8),4)=2)),"F0","P all"))
or even slightly shorter
=IF(COUNT(J8,N8,R8,V8,Z8,AD8)=0,"",SUBSTITUTE("F"&SUMPRODUCT((J8:AD8<35)*(MOD(COLUMN(J8:AD8),4)=2)),"F0","P all")) a bit shorter still
=IF(COUNT(J8,N8,R8,V8,Z8,AD8),SUBSTITUTE("F"&SUMPRODUCT((J8:AD8<35)*(MOD(COLUMN(J8:AD8),4)=2)),"F0","P all"),"")