Discussion Locked

Excel nexted functions

0 helpful
7 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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?

7 replies

by deakb

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

by blginger

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

by blginger

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

by blginger

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

This discussion is closed to new replies.