# Software

## General discussion

Locked

### Excel nexted functions

By ·
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.

Thread display: Collapse - | Expand +

Collapse -
by In reply to Excel nexted functions

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 -

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 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 -

Collapse -
by 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 -

Collapse -
by In reply to Excel nexted functions

This question was closed by the author

• 0

• 1

SunnyYam ·

• 3

blyn17 ·

• 2

peggyliao ·

• 2

Alonshow ·