Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I identify the two lowest values in a row?
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... |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I identify the two lowest values in a row?
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 "jaysmith80" wrote: 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... |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I identify the two lowest values in a row?
Wow! Thanks so much. That is really helpful. You don't want to know how
much time you saved me. Thanks, Jay "JulieD" wrote: 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 "jaysmith80" wrote: 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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Keeping a cell value constant trhoughout a list of values | Excel Worksheet Functions | |||
Count Intervals of 2 Consecutive Values in same Row and Return Count across Row | Excel Worksheet Functions | |||
I Need a formula to evaluate a cell with + or - values | Excel Worksheet Functions | |||
Return Range of Numerical Values in Single Column based on Frequency Percentage | Excel Worksheet Functions | |||
#N/A Values : Returned by Formulas vs Entered Manually | Charts and Charting in Excel |