How can I identify the two lowest values in a row?

[Guest]

I am a teaching assistant and maintain a spreadsheet for grades. I remove
the two lowest homework grades manually within each student's row. Surely,
there is a way to identify those two lowest values with a formula...

 

[Guest]

Hi Jaysmith80

Depending on what you really want to do I can see at least two ways to
approach your question
1) if you want the total of a row of numbers excluding the two lowest one
formula you can use is
=SUM(A1:J1)-SUM(SMALL(A1:J1,1),SMALL(A1:J1,2))

(assuming that the numbers are in the range A1:J1)

2) if you want the lowest two values on each row highlighted you can use
conditional formatting (based on the assumption that the student values are
in the rows A1:J8)
- highlight the rows from A1 to J8, ensuring that A1 is the active cell
- choose format / conditional formatting
- change "cell value is" to "formula is"
- in the big white line type
=OR(SMALL($A1:$J1,1)=A1,SMALL($A1:$J1,2)=A1)

- click on the format button, choose font colour (or whatever) for the
lowest values to be displayed in.
- click OK twice, and the two lowest values should now be formatted
differently.

hope this helps.

Cheers
JulieD

julied at hctsReMoVeThIs dot net dot au

 

[Guest]

Wow! Thanks so much. That is really helpful. You don't want to know how
much time you saved me.

Thanks,

Jay