SOLVED

New Contributor

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

5 Replies

# Re: Calculating values based on position in family

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.

# Re: Calculating values based on position in family

@Hans Vogelaar 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

best response confirmed by Zululander (New Contributor)
Solution

# Re: Calculating values based on position in family

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.

# Re: Calculating values based on position in family

Alternatively:

=INDEX(\$B\$1:\$B\$5,MIN(COUNTIF(\$C\$8:\$C8,\$C8),5))

# Re: Calculating values based on position in family

lol, I can't believe I did that thanks