1

Very new to Google sheets and very lost.

I am trying to create an awards tracker for school Merits 2024

Ideally, I want to include drop down menus with options for each week we hand out merits at school e.g. Week 1, Week 2 etc. Depending on the week, these awards are worth either 5 or 10 points. (Wk 4 and 7 are 10 points, all other weeks are 5 points.)

My aim is to then have a total column that keeps track of how many points students should have (Column B in example.) E.g. If I get a merit in Week 4, then 10 points gets added to the total in column B.

I have also added conditional formatting to this column so that depending on the points, I know what level they are on e.g. bronze, silver and gold.

Any help would be greatly appreciated

1
  • Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer.
    – Community Bot
    Commented Apr 8 at 5:52

2 Answers 2

1

One way is to use map() and ifs(), like this:

=sum(map(D2:2, lambda(week, ifs( 
  week = "N/A", 0, 
  week = "", 0, 
  week = "Week 4", 10, 
  week = "Week 7", 10, 
  true, 5    
))))

See map() and ifs().

2
  • Thank you! That worked!
    – MHLO17
    Commented Apr 9 at 6:20
  • Thanks. Yes and I am very lost haha
    – MHLO17
    Commented Apr 10 at 1:35
0

Using REGEXMATCH

# SYNTAX 
=REGEXMATCH(text, regular_expression)

Single Row Formula

Needs to be copied down for each row.

=INDEX(
   SUM( REGEXMATCH(D2:E2, "4|7") * 10,
        REGEXMATCH(D2:E2, "[^47\D]") * 5)) 

Multi-Row Formula

BYROW is added so a single LAMBDA's formula can return the results for all rows that have a student listed.

=BYROW( FILTER(D2:E, A2:A<>""), LAMBDA(r,
     INDEX(
       SUM( REGEXMATCH(r, "4|7") * 10,
            REGEXMATCH(r, "[^47\D]") * 5))

Explanation

Regular Expressions

  1. REGEXMATCH is used within INDEX to return TRUE/FALSE values for the range (row) rather than just one cell:

    # Single Cell Version 
    =REGEXMATCH({"Week 7", "Week 2"}, "4|7")=TRUE
    =REGEXMATCH({"Week 7", "Week 2"}, "[^47\D]")=FALSE
    # INDEX (Array) Version =INDEX(REGEXMATCH({"Week 7", "Week 2"}, "4|7"))={TRUE, FALSE} =INDEX(REGEXMATCH({"Week 7", "Week 2"}, "[^47\D]"))={FALSE, TRUE}
  2. Sheets will coerce TRUE & FALSE to 1 & 0 if used in a calculation that requires digits. This behavior is leveraged to multiply the resulting array of matches for 4 or 7 by 10 and the other matched numbers by 5:

    # TRUE & FALSE to 1 & 0 
    =INDEX(REGEXMATCH({"Week 7", "Week 2"}, "4|7")*10)
    =INDEX({TRUE, FALSE}*10)
    ={1*10, 0*10}
    ={10, 0}
    =INDEX(REGEXMATCH({"Week 7", "Week 2"}, "[^47\D]") * 5) =INDEX({FALSE, TRUE} * 5) ={0*5, 1  *5} ={0, 5}
  3. All that remains is to SUM the two arrays to get the total.

    # Where range={"Week 7", "Week 2"} 
    =INDEX(SUM(REGEXMATCH(range, "4|7") *10), REGEXMATCH(range, "[^47\D]") *5)
    =INDEX(SUM({TRUE, FALSE}*10, {FALSE, TRUE} * 5))
    =INDEX(SUM({10, 0},{0, 5}))
    =15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.