+ Reply to Thread
Results 1 to 6 of 6

Conditional IF when arrays are arranged differently

  1. #1
    Registered User
    Join Date
    10-07-2015
    Location
    San Jose, CA
    MS-Off Ver
    2013
    Posts
    10

    Conditional IF when arrays are arranged differently

    I need to add multiple conditions and looking for a formula that summarizes the result. Please see the excel spreadsheet attached. Basically, I would like to add conditions for each scenario.
    In the table below I would like to get a summary table by applying a formula based on the several conditions.They are

    IF all the three (Self, Internal and External) are present then the Formula should be(20%Self+10%Internal+30%External)
    IF only External and Internal are present then the formula should be (70%external+ 30% internal)
    If only External and self are present then the formula should be (70% external+30% Internal)
    If only Internal and self are present then the formula should be (30% Internal+70% self)
    If only one type of Nomination (Self, Internal or External) if present then. Apply 100% of that.

    Employee ID Nomination Part 1 Part 2 Part 3 Part 4 Part 5 Part 6 Part 7
    1 Self 4.0 2.0 4.0 4.0
    1 Internal 2.0 2.0 4.0 3.0
    1 External 3.0 2.0 1.0 2.0
    result 3.1 2.0 2.0 2.0 1.9 2.6 3.7
    2 Self 2.0 4.0 3.0 5.0
    2 Internal 3.0 3.0 6.0 2.0
    2 External 5.0 2.0 2.0 2.0
    result 0.3 4.2 3.2 2 4 2.3 4.1

    Employee ID Summary
    1 3.1 2 2 2 1.9 2.6 3.7
    2 0.3 4.2 3.2 2 4 2.3 4.1
    Attached Files Attached Files

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Conditional IF when arrays are arranged differently

    Hi,

    one way in C5 copied across

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    ...and no doubt there'll be a more elegant one along in a moment.

  3. #3
    Registered User
    Join Date
    10-07-2015
    Location
    San Jose, CA
    MS-Off Ver
    2013
    Posts
    10

    Re: Conditional IF when arrays are arranged differently

    Thanks!
    How do I get that result in C12?
    Thanks for your help

  4. #4
    Registered User
    Join Date
    10-07-2015
    Location
    San Jose, CA
    MS-Off Ver
    2013
    Posts
    10

    Re: Conditional IF when arrays are arranged differently

    I mean as a summary table like C12:I13

  5. #5
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Conditional IF when arrays are arranged differently

    Then create a summary table by adding your individual tables together and use the formula I gave you. It will work on any table that has the same layout.

  6. #6
    Registered User
    Join Date
    10-07-2015
    Location
    San Jose, CA
    MS-Off Ver
    2013
    Posts
    10

    Re: Conditional IF when arrays are arranged differently

    Wow! This worked beautifully! Thanks a Lot!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 9
    Last Post: 07-17-2015, 04:08 PM
  2. Conditional rank in VBA, using Variant arrays
    By nchmy in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-05-2013, 01:55 PM
  3. Conditional averaging formula using arrays?
    By mrvp in forum Excel General
    Replies: 1
    Last Post: 11-13-2011, 08:42 AM
  4. UDF that includes conditional arrays
    By JonnyB in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-13-2011, 03:17 PM
  5. Multiple sheets with same data arranged differently
    By hozey in forum Excel General
    Replies: 6
    Last Post: 10-06-2010, 12:08 PM
  6. Arrays in Conditional Formatting
    By Dancin in forum Excel General
    Replies: 4
    Last Post: 10-12-2009, 06:20 AM
  7. [SOLVED] Using Conditional Sums/Averages on Arrays
    By ExcelMonkey in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-23-2006, 10:25 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1