Forum Discussion
Solved
Calculating values based on position in family
Our school SMS exports data as follows
| Student - Legal Name | Group 1 - Name | Edge Name | Fee |
| Jon Smith | Auckland Campus | Peter and Sue Smith | 0 |
| Sue Smith | Auckland Campus | Peter and Sue Smith | 0 |
| Angie Brown | Nelson Campus | James and Jackie Brown | 0 |
| Tom Rivers | Nelson Campus | Jon and Judy Rivers | 0 |
| Judy Rivers | Nelson Campus | Jon and Judy Rivers | 0 |
| Storm Rivers | Nelson Campus | Jon and Judy Rivers | 0 |
| Lawson White | Rangiora Campus | Alan and Syliva White | 0 |
| Ted Foster | Maungaturoto Campus | Steve and Kim Foster | 0 |
| Louis Crawshaw | Hastings Campus | Albert and Peta Crawford | 0 |
| Brett Stone | Hamilton Campus | Alan and Sarah Stone | 0 |
| Karen Shaw | Auckland Campus | Andre and Emma Shaw | 0 |
| David Shaw | Auckland Campus | Andre and Emma Shaw | 0 |
| Suzzie Banks | Auckland Campus | Fred and Simon Banks | 0 |
| Trevor Banks | Auckland Campus | Fred and Simon Banks | 0 |
| Anna Banks | Hastings Campus | Fred and Simon Banks | 0 |
| Kate Banks | Hastings Campus | Fred and Simon Banks | 0 |
| Phil Green | Hawera Campus | Andrew and Kim Green | 0 |
We are needing the Fee colum to be auto populated as follows i.e. the First child in the family gets charged Fee1, 2nd Fee2 etc up until Fee5 (Which is the lost fee possible). What forumlar can I use to vreate this.
I had assumed that the data began in row 2. Your data begin in row 8, so you should use
=CHOOSE(MIN(COUNTIF($C$8:$C8,$C8),5),$B$1,$B$2,$B$3,$B$4,$B$5)
in D8, then fill down.
5 Replies
In D2:
=CHOOSE(MIN(COUNTIF($C$2:$C2,$C2),5),Fee1,Fee2,Fee3,Fee4,Fee5)
Replace Fee1 to Fee5 with the actual fee amounts.
Then fill down.
HansVogelaar Thanks for this but I am still doing something wrong.
I used
=CHOOSE(MIN(COUNTIF($C$2:$C2,$C2),5),$B$1,$B$2,$B$3,$B$4,$B$5)
And get the following
