CODEWITHSUNDEEP
excelvba
Sam
Sam

Reputation: 37

How to 'group' data in excel and find the maximum in the group

I have parent and child accounts that make a family. If the Parent_acct = Accnt_no then its parent. I need to find the maximum Term in the family and show as Family_term (this is to be calculated) for each account. What's the good way to do it? Sorry cannot post an image yet.

        Parent_acct_no   Account_no    Type    Term         Family_term(max)
        565              565           Parent  45           78
        565              256           Child   78           78
        331              331           Parent  23           87
        331              754           Child   87           87
        331              823           Child   19           87

Upvotes: 2

Views: 9890

Answers (2)

dgw
dgw

Reputation: 13646

Lets assume the following columns:

A               B            C     D      E
Parent_acct_no  Account_no   Type  Term   Family_term(max)

And you want to sum up Family_term grouped by each Type=Parent then you could use the following formula in Column F

(F2) =if(c2="Parent";sumif(A$2:A$500;B2;E$2:E$500);"")

If you name the column A (P_acc=A$2:A$500) and E (F_term=E$2:E$500) you can write

(F2) =if(c2="Parent";sumif(P_acc;B2;F_term);"")

Since I don't have access to excel at home I could not test it tho.

Hope that helps.

Edit: Corrected my denglish excel command (when->if) Thx.

Upvotes: 0

vasek1
vasek1

Reputation: 14071

You can use an array formula of max combined with if. Assume the columns are A (Parent_Account_No) through E (Family term max), the formula in column E should be (starting with E2):

{=max(if($A$2:$A$6=A2,$D$2:$D$6,FALSE))}

Make sure to press Ctrl+Shift+Enter when entering the formula to make it an array formula (see details here: http://www.cpearson.com/excel/ArrayFormulas.aspx)

Upvotes: 7

Related Questions