Teacher Tech blog with Alice Keeler

Paperless Is Not a Pedagogy

Alice Keeler

IF you use Google Sheets

If Statements Google Sheets
IF you use Google Sheets

If Statements Google Sheets

IF Function in Google Sheets

One of my favorite formulas for a spreadsheet is an IF function. If the value of a cell meets certain criteria, do this. Otherwise, do something else.

If

=IF(criteria, true, false)

Examples

Random Sort

I like to use the formula =rand() to assign students a random number and then sort by the random number to choose a student randomly. Doing this allows for the same student to be chosen repeatedly since they are put back in the pool. If you want to draw without replacement, use an IF function.

Create a column to mark off students who have been chosen.
call on students and mark them off

In the random column,  you want to assign a random number IF there is no x in the called on column. In my example spreadsheet, I have “Name” in column A, “Called on” in column B, and “Random” in column C. In column C I put

=if(B2=””,rand(),””).

Which says if what is in cell B2 is blank (need double quotations for that), put the formula rand() in cell C2. Otherwise, have C2 be blank (need double quotations for that).
Called on randomly

Scores

What if a student has a low score and you want to be alerted to that. If a score is below a certain number you may be concerned about that student. Otherwise, no action.
Low score

In column C I put

=IF(B2<65,”Concerned”,””)

Which says, if the value in cell B2 is less than 65, put the word concerned in column C. Note that all text strings must be in quotations, so the word Concerned needs to be expressed as “Concerned.” Once again, leaving the cell blank would be double quotations.
if score is low write concerned

Check for Duplicates

I will frequently sort a list of data to see if there are duplicate entries. Manually checking for duplicates is flawed, so I use a formula. First sort the column of data that potentially has repeated information, this puts the repeats next to each other. In another column write an IF statement.

=IF(A2=A1,”ALERT”,””)

This checks to see if the value in the row above is the same. If it is the same, put the word “ALERT” in column B. Otherwise, do nothing. Click back on the cell with the formula and use the fill down feature to copy the formula to the entire list.
Find duplicates

Other Ideas

I get pretty fancy with my IF statements sometimes. For another blog post, I do nested IF statements such as

=IF(A2<60,”F”,IF(A2<70,”D”,IF(A2<80,”C”,IF(A2<90,”B”,”A”))))

How do you use IF statements? Leave your suggestions in the comments below.



6 thoughts on “IF you use Google Sheets

  1. I use if statements to calculate pay with overtime rates. For example, if Mary works 42 hours a week, paid $20 an hour, and is paid time and a half for any hours over 37 hours, what is Mary’s gross weekly pay?

    b5=number of hours worked
    C5 = hourly rate

    =if(B5>37,37*C5+(B5-37)*C5*1.5,B5*C5)

  2. I have enjoyed making my spread sheets do more using IF, IFERROR and also VLOOKUP, I have used =IF(B3=”FAIL”,0,IF(B3=”LEVEL 1″,6,IF(B3=”PASS” ,12,IF(B3=”MERIT”,18,IF(B3=”DISTINCTION” , 24))))) this has been used in a grading tracker for BTEC Sport.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

© 2024 All Rights Reserved.

💥 FREE OTIS WORKSHOP

Join Alice Keeler, Thursday Mar 28th or register to gain access to the recording.
Create a free OTIS account.

Join Alice Keeler for this session for a way to create dynamic and interactive digital lessons. The Desmos platform is completely free and allows for any topic to be created or customized.

Exit this pop up by pressing escape or clicking anywhere off the pop up.