Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Max and Min in a Range
I've got a 300 row spreadsheet and I want to calculate the Max and Min
values in Column A based on an integer in Column B. In the example below I'd like to be able to report Max(11) is 230; Min(11) is 211; Max(10) is 200; Max(10) is 195. Columns A and B will always be integers in descending order. Can SKS help out Tx Rob 230 11 226 11 220 11 220 11 211 11 200 10 198 10 196 10 195 10 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Max and Min in a Range
You could do it with a couple of array* formulae. Put the labels Max
and Min in D1 and E1, and put 10 and 11 in C2 and C3, then put this array* formula in D2: =MAX(IF(B$1:B$300=C2,A$1:A$300)) and put this array* formula in E2: =MIN(IF(B$1:B$300=C2,A$1:A$300,10E10)) * As these are array formulae then you must use CTRL-SHIFT-ENTER (CSE) to commit them rather than the usual ENTER. If you do this correctly then Excel will wrap curly braces { } around the formula when viewed in the formula bar - you must not type these yourself. If you edit/ amend the formula, you must use CSE again to commit it. Copy the two formulae into D3:E3, and then you will have your results in a little table. Hope this helps. Pete On Apr 28, 4:20*pm, "BRob" wrote: I've got a 300 row spreadsheet and I want to calculate the Max and Min values in Column A based on an integer in Column B. In the example below I'd like to be able to report Max(11) is 230; Min(11) is 211; Max(10) is 200; Max(10) is 195. Columns A and B will always be integers in descending order. Can SKS help out Tx Rob * * * 230 11 * * * 226 11 * * * 220 11 * * * 220 11 * * * 211 11 * * * 200 10 * * * 198 10 * * * 196 10 * * * 195 10 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
When entering data into a range of cells, select the entire range. | Excel Discussion (Misc queries) | |||
Excel Addin:Setting the range to the Excel.Range object range prop | Excel Worksheet Functions | |||
Selecting range in list of range names depending on a cell informa | Excel Discussion (Misc queries) | |||
formula to sort a range so that it matches the exact rows of a column that is outside that range? | Excel Discussion (Misc queries) | |||
How to count dates within a certain range in a column with mutiple date range entries | Excel Worksheet Functions |