Results 1 to 6 of 6

Thread: Calculation with different condition in a cell

  1. #1
    Senior Member LalitPandey87's Avatar
    Join Date
    Sep 2011
    Posts
    222
    Rep Power
    13

    Calculation with different condition in a cell

    Hi All,

    I have some data ex:


    Category Sub Category Values
    asd 2
    asd 4
    edf 2
    asd 3
    aec a 2
    edf a 1
    aef a 2
    qwe a 1
    aef a 4


    i need some formula or VBA which can do the calculation according to below condition:

    1:- If Category contains value but sub cat. doesn't and there is only single entry for this then the output will be the value of the entry
    2:- If Category contains value but sub cat. doesn't and there is more then one entry for this then the output will be the average of the values
    3:- If Category and sub cat. both contains value and there is only single entry for this then the output will be the value of the entry
    4:- If Category and sub cat. both contains value and there is more then one entry for this then the output will be the average of the values
    5:- If Category and sub cat. both contain values and category are same but sub cat. are differ then the output will be the value of the entry

    and Output will be like this:

    Category Sub Category Values OutPut
    asd 2 3
    asd 4 3
    edf 2 2
    asd 3 3
    aec a 2 2
    edf a 1 1
    aef a 2 3
    qwe a 1 1
    aef a 4 3


    Thanks in Advance.
    Last edited by LalitPandey87; 04-03-2012 at 06:51 PM.

  2. #2
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Give this formula a try...

    =SUMPRODUCT((A$2:A$10&B$2:B$10=A2&B2)*C$2:C$10)/SUMPRODUCT(1*(A$2:A$10&B$2:B$10=A2&B2))

  3. #3
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by Rick Rothstein View Post
    Give this formula a try...

    =SUMPRODUCT((A$2:A$10&B$2:B$10=A2&B2)*C$2:C$10)/SUMPRODUCT(1*(A$2:A$10&B$2:B$10=A2&B2))
    Actually, in thinking about it a little more, to avoid the possibility of a false match between those concatenations, use this modification of the formula instead...

    =SUMPRODUCT((A$2:A$10&CHAR(1)&B$2:B$10=A2&CHAR(1)& B2)*C$2:C$10)/SUMPRODUCT(1*(A$2:A$10&CHAR(1)&B$2:B$10=A2&CHAR(1) &B2))

  4. #4
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    Hi

    Assume your data in A2:C10, in D2 and copied down,

    =AVERAGE(IF($A$2:$A$10=A2,IF($B$2:$B$10=B2,$C$2:$C $10)))

    It's an array formula. Confirmed witH CTRL + SHIFT + ENTER
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  5. #5
    Senior Member LalitPandey87's Avatar
    Join Date
    Sep 2011
    Posts
    222
    Rep Power
    13
    Thanks Rick and Admin. This is what i want all working fine for me.

  6. #6
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    Hi Lalit,

    Thanks for the feedback
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

Similar Threads

  1. Deduct Highests Scores By Condition
    By antonio in forum Excel Help
    Replies: 3
    Last Post: 06-08-2013, 06:20 PM
  2. Formula Following Order Of Calculation BODMAS
    By paul_pearson in forum Excel Help
    Replies: 7
    Last Post: 03-27-2013, 02:07 PM
  3. Hours Calculation between two times
    By excel_learner in forum Excel Help
    Replies: 3
    Last Post: 11-21-2012, 05:04 PM
  4. Formula Based On Condition
    By Aryan063007 in forum Excel Help
    Replies: 4
    Last Post: 10-09-2012, 10:37 AM
  5. Excel Nested IF 3 Condition Formula
    By yomgi in forum Excel Help
    Replies: 2
    Last Post: 02-20-2012, 11:50 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •