General discussion

Locked

Excel nexted functions

By blginger ·
I'm trying to count student attendance if they are Special Education students, ie =if(f15="P"+ae15="1"),1,0 (if one cell is "P" AND another cell is "1" then return "1")
This returns "invalid" or "value" and function doesn't work. When I get the function to work, it returns an incorrect value. How can I get this to work?

This conversation is currently closed to new comments.

7 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by DEAKB In reply to Excel nexted functions

How about this approach?
Column "A" (contains your "P") and column "B" (contains your "1") contains your data, both on row one in this example. In coulmn "C" you type "=AND(A1=P;B1=1)". This returns TRUE if the AND-condition is true. Expand this down column "C" to cover the number of rows you have your input on (e.g. "C2" will have the formula "=AND(A2=P;B2=1)")
In another cell, where you want the amount of special students displayed, you type "=COUNTIF(C:C;TRUE)".

COUNTIF will now count all cells in column "C" that contains TRUE.

If you want to expand this the formula in column "C" could by "=IF(AND(A1=P;B1=1);"Special student";"Ordinary student"). This would return the text "Special student" in cell C1 if the statment is TRUE. Then you change the COUNTIF to "=COUNTIF(C:C;"Special student")". Noe th eCOUNTIF will count the numbers of cells that have the text "Special student" in column "C".

Hope this makes some sense to you. It's bl***y hard to explain in plain text.

TIP:Use the menu Insert - Function... (symbol is a "fx") and search for the function. The help text displayed once you selected a function in the wizard is great and tells you how the function works in detail. The best functions I use is COUNTIF (suprise) and VLOOKUP. Both are great to manage tables of data.

Good luck. //Dan-Erik

Collapse -

by blginger In reply to

Tried the second suggestion "=COUNTIF(AE15,"1"+IF(F15="P",0,1))" with a few adaptations and IT WORKED!!! It really IS Christmas! Thanks!

Collapse -

by blginger In reply to Excel nexted functions

Sounds good, but "=AND(A1=P;B1=1)" returns a #NAME? error for me (I'm using Office 2000). Any other suggestions?

Collapse -

by blginger In reply to

Poster rated this answer.

Collapse -

by blginger In reply to Excel nexted functions

Sounds good, but "=AND(A1=P;B1=1)" returns a #NAME? error for me (I'm using Office 2000). Any other suggestions?

Collapse -

by blginger In reply to

Poster rated this answer.

Collapse -

by blginger In reply to Excel nexted functions

This question was closed by the author

Back to Software Forum
7 total posts (Page 1 of 1)  

Related Discussions

Related Forums