Extracting nth value

[Ufoo]

Hello excel experts,

I have not nagged you for a while. I have a problem in the attached excel sheet.
1) In the sheet named RIba I have tried to extract the 1st, 2nd riba etc for the person named on column B, from the sheet Leja ya kila siku. But so far I am getting it wrong. Please help

2) I am managing a small serving and credit scheme. We want to factor in time in distribution of interest; such that if you joined late (in months) you will not be on the same foot as a person who was in the scheme from the beggining. I have failed in that also. Please help. I hope my problem is clear. Thanks.

 

[bosco_yip]

Your array formula (Ctrl+Shift+Enter), in "Riba"sheet C3 revised as below, then copied across and down :

=IFERROR(INDEX(Riba,SMALL(IF((Muamala="mkopo")*(Mhusika=$B3),ROW(Muamala)-MIN(ROW(Muamala))+1),COLUMNS($C3:C3))),"")

Regards
Bosco

 

[Ufoo]

Your array formula (Ctrl+Shift+Enter), in "Riba"sheet C3 revised as below, then copied across and down :

=IFERROR(INDEX(Riba,SMALL(IF((Muamala="mkopo")*(Mhusika=$B3),ROW(Muamala)-MIN(ROW(Muamala))+1),COLUMNS($C3:C3))),"")

Regards
Bosco

Thanks @Bosco. It was naive of me not to lock columns

 

[Ufoo]

Let me elaborate the second part of my question. Please see the attached sheet. Is a way of designing a formula at I2 to I5 which will factor in number of months to calculate distribution according to the amount which every person has contributed? SUM works yes, but a formula which factors in number of months would be better for my problem. Thanks

 

[Hui]

I2: =$H$6*COUNT(C2:H2)/COUNT($C$2:$H$5)
copy down

This is only accurate if they all contribute the same amount each month

If they don't I'd use a straight sum(C2:H2)

 

[Ufoo]

I2: =$H$6*COUNT(C2:H2)/COUNT($C$2:$H$5)
copy down

This is only accurate if they all contribute the same amount each month

If they don't I'd use a straight sum(C2:H2)

Thanks a lot ninja