qwzky
Board Regular
- Joined
- Jul 22, 2021
- Messages
- 53
- Office Version
- 2021
- 2016
- Platform
- Windows
Hi! I've been trying for two days to get this out, and now I'm starting to think that what I want is actually impossible. Maybe I'll find my luck here 
I have the following table, where I included my criteria in red:
I have tried to use COUNTIF and AND, but still can't get it work.
My criteria are:
1. Numbers from Grade 1 to Grade 5 should be higher than 4 for average (if not, leave the average cell empty)
2. Numbers in OP should be higher than 5 (if not, leave the average cell empty)
3. If OP has empty cells, don't calculate average at all.
3. In calculating average, look for text (if there is text, leave the average cell empty)
4. In calculating average, ignore empty cells (for instance, John's average should be: (6+6+6+7+6)/5, not divided by 6)
As you see, I have 2 criteria that are somewhat contradictory: ignore empty cell in a range, but take into account the empty cell in another range.
I have the following table, where I included my criteria in red:
| SC-uri automate REDONE4 (test, ă văd dacă merge să lucrez in sheets separate).xlsm | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| B | C | D | E | F | G | H | I | J | |||
| 3 | Students | Grade 1 | Grade 2 | Grade 3 | Grade 4 | Grade 5 | Overall points | Average | |||
| 4 | Not appliable yet | #VALUE! | |||||||||
| 5 | Mark | 4 | 5 | 7 | 7 | 5 | 5 | don't calculate because Grade1<5 AND because OverallPoints<6 | |||
| 6 | John | 6 | 6 | 6 | 7 | 6 | calculate because Grade 1 ... Grade 5 >=5 AND because OverallPoints>=6 AND ignore Grade 5 (empty cell) | ||||
| 7 | Anne | abs | 5 | 6 | 5 | don't calculate because she was absent at Grade1 AND because she doesn't have OverallPoints | |||||
| 8 | James | 10 | 10 | 10 | 10 | 5 | don't calculate because OverallPoints<6 AND ignore Grade 5 (empty cell) | ||||
CIORNĂ | |||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| J4 | J4 | =AVERAGEIFS(C5:H5,C5:G5,">=5",H5,">=6") |
I have tried to use COUNTIF and AND, but still can't get it work.
My criteria are:
1. Numbers from Grade 1 to Grade 5 should be higher than 4 for average (if not, leave the average cell empty)
2. Numbers in OP should be higher than 5 (if not, leave the average cell empty)
3. If OP has empty cells, don't calculate average at all.
3. In calculating average, look for text (if there is text, leave the average cell empty)
4. In calculating average, ignore empty cells (for instance, John's average should be: (6+6+6+7+6)/5, not divided by 6)
As you see, I have 2 criteria that are somewhat contradictory: ignore empty cell in a range, but take into account the empty cell in another range.
