Results 1 to 7 of 7

Use Index Match or lookup to create array from array

Threaded View

  1. #1
    Registered User
    Join Date
    08-01-2018
    Location
    Riverside, California
    MS-Off Ver
    365
    Posts
    4

    Use Index Match or lookup to create array from array

    Hello! This is my first post so I haven't figured out how to do like, formatting. so bear with me.

    I am trying to create a somewhat complex gradebook for use in automating some course grade calculations for my students. I am running into a hiccup at a stage in which I have codes to designate whether assignments or participation receive full, partial, or no credit (e.g. a late assignment receives 50% of the points they would have received if it had been on time.

    It's a trivial exercise to create an array of the codes I will input; Where I am struggling is with creating the array of the desired values from the codes. I'm down to use the name manager if that will simplify things.

    Also, because the prof is a strange fellow, I need this to be semi automatic with codes-> values, ie. if I end up adding more codes (late by an hour is 90% for example) that I don't need to go though and manually enter new criteria, but that I can just change the range of my code/value table.

    In bad forum text, because I haven't figured out tables, I have a sampling of what I have and want (manually entered).

    [Participation]
    Code Value
    P 1.0
    T 0.5
    U 0.0

    Table 1 Arrays Codes Desired Array Values
    1 2 3 4 5
    Student1 P P P P P {P,P,P,P,P} {1.0,1.0,1.0,1.0,1.0}
    Student2 U P T P P {U,P,T,P,P} {0.0,1.0,0.5,1.0,1.0}
    Student3 P P P P P {P,P,P,P,P} {1.0,1.0,1.0,1.0,1.0}
    Student4 P T P T P {P,T,P,T,P} {1.0,0.5,1.0,0.5,1.0}
    Student5 P T P P P {P,T,P,P,P} {1.0,0.5,1.0,1.0,1.0}

    What I've concocted so far is

    =INDEX(Participation,MATCH(C11,Participation[Code],0),MATCH("Value",Participation[#Headers],0))

    Which works well to catch individual cells, but I don't know how to get something that I can use on the array to produce an array of values in a single expression or cell (Which will need to be multiplied by points possible array, so yes I believe I need an array of values) Of Note here, I believe I would like a 1D array of values for each student to make the later sumproduct easier.

    Anyway, any assistance is GREATLY appreciated!!
    Attached Files Attached Files
    Last edited by kinemagichemistry; 08-01-2018 at 02:10 PM. Reason: Clarification.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 5
    Last Post: 02-22-2018, 01:47 AM
  2. Replies: 3
    Last Post: 04-02-2016, 08:16 PM
  3. [SOLVED] Match-Index in stead of Index-Match lookup Array among Arrays
    By Numnum in forum Excel General
    Replies: 2
    Last Post: 10-15-2015, 02:08 PM
  4. Replies: 6
    Last Post: 04-30-2014, 02:42 AM
  5. Lookup, Index, Match, Array Help
    By Reaye in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-06-2013, 03:36 PM
  6. Replies: 7
    Last Post: 06-19-2011, 12:51 PM
  7. table, index, array, match, lookup?
    By spxer in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-08-2006, 04:35 PM

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