SOLVED

Calculating values based on position in family

New Contributor

Our school SMS exports data as follows

 

Student - Legal NameGroup 1 - NameEdge NameFee
Jon SmithAuckland CampusPeter and Sue Smith0
Sue SmithAuckland CampusPeter and Sue Smith0
Angie BrownNelson CampusJames and Jackie Brown0
Tom RiversNelson CampusJon and Judy Rivers0
Judy RiversNelson CampusJon and Judy Rivers0
Storm RiversNelson CampusJon and Judy Rivers0
Lawson WhiteRangiora CampusAlan and Syliva White0
Ted FosterMaungaturoto CampusSteve and Kim Foster0
Louis CrawshawHastings CampusAlbert and Peta Crawford0
Brett StoneHamilton CampusAlan and Sarah Stone0
Karen ShawAuckland CampusAndre and Emma Shaw0
David ShawAuckland CampusAndre and Emma Shaw0
Suzzie BanksAuckland CampusFred and Simon Banks0
Trevor BanksAuckland CampusFred and Simon Banks0
Anna BanksHastings CampusFred and Simon Banks0
Kate BanksHastings CampusFred and Simon Banks0
Phil GreenHawera CampusAndrew and Kim  Green0

 

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

@Zululander 

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.

@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

Zululander_2-1660514338646.png

 

 

 

 

best response confirmed by Zululander (New Contributor)
Solution

@Zululander 

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.

@Zululander 

Alternatively:

 

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

lol, I can't believe I did that thanks
www.000webhost.com