Hello Addisonbr,
You want to use something called an "array formula" to do what you
ask. You can get an explanation of array formulas by searching the
Excel help with the phrase
array
or
array formula
A brief explanation on my system is under an entry titled "About Array
Formulas and How to Enter Them". You help file may be slightly
different - check for similar names if this one does not appear. Note
in particular the way you "enter" the formula. There are a few
examples under this entry as well - one very similar to the one I show
below.
The formulas I entered into cell E2 using your sample data was...
=MAX(IF($A$2:$A$10=$A2,B$2:B$10,0))
though after you enter this as an array formula, it will be shown as
{=MAX(IF($A$2:$A$10=$A2,B$2:B$10,0))}
in the formula bar. Cell E2 shows the value 23 after entry.
If you get an error at this point, make sure the formula is typed
correctly and that it is entered properly (entered without {}, but
displays with {}). Copying and pasting the "formula I entered" into
the formula bar when cell E2 is selected and then doing the "array
formula entry" should work - let me know if it does not. If you are
doing your own examples, one possible problem occurs if the ranges of
values don't have the same number of entries (in this case 2:10 or
nine) in each range.
Don't do this next step until the formula is correct. Select the range
E2 through G10 and do a "Fill Down" (Edit -> Fill -> Down) and "Fill
Right" (Edit -> Fill -> Right). You will see all the values shown as
their proper values. Sounds pretty easy, doesn't it. The reason it
works is because of the funny references described below.
Let me explain how this works. First the references...
- $A$2:$A$10 is an "absolute reference" to the first column of names.
This value will not change when you do the fill down nor fill right.
If you click on any of the cells with the formula, you will see this
in the formula bar.
- $A2 is an absolute reference to the first column and a relative
reference to the current row. Each row will show something like $A3,
$A4, and so on up to $A10. This makes the IF function apply to the
name in the first column on the current row.
- B$2:B$10 is a relative reference to column B and absolute reference
to rows 2 through 10. If you click on a cell in column F, this will
appear as C$2:C$10; in column G, this will appear as D$2:D$10. This is
how each column has a different value passed to the MAX function.
- 0 is used as the "smallest value" in this case. Depending on the
type of data you are analyzing, make it a large positive number if
using MIN and a large negative number if using MAX.
Note that if you just select a region with Excel, it is usually has
relative references. This does not help you when using the two fill
operations and you have to fix the references in the formula bar to
"make them right".
The IF function is also described in Excel help, but briefly if the
first expression ($A$2:$A$10=$A2) is true, the first value (B$2:B$10)
is returned, otherwise the second value (0) is returned. In cell E2,
that value is true only for the first three rows of data and false for
the remaining items. Since this is an array formula, in cell E2, we
get an array result
{23,23,22,0,0,0,0,0,0}
In other cells, you get similar values based on the relative /
absolute references described above.
The MAX function works as you expect for the array result. In cell E2,
we get a single valued result of 23. Any function like MIN, AVERAGE,
and others that operate with array values and return a single value
will work in a similar manner.
If you know how to define names, you can replace the absolute /
relative references with a name - let me know if you want to use an
enhancement like that.
If any of this is unclear or you want to pursue some related
enhancements (such as using names), please use a clarification
request.
--Maniac |
Clarification of Answer by
maniac-ga
on
22 May 2003 05:31 PDT
Hello Addisonbr,
I wish I had seen that restriction earlier; I will attempt to read
future questions more fully. To aid the researchers reading your
questions, please place a restriction like that on a line by itself so
it appears more clearly. I also ask you to request a question
clarification in cases like this one; it allows the researcher to
retract an answer.
I apologize for the incorrect answer and to correct it I offer the
following:
Using Visual Basic, enter the following function
Function IfMax(Selector, Match, Value)
'
' IfMax Macro
' Macro created 5/22/2003 by Maniac
'
'
Max = Value.Cells(1).Value
For N = 1 To Match.Count
If (Match.Cells(N).Value = Selector.Value) Then
If (Max < Value.Cells(N).Value) Then
Max = Value.Cells(N).Value
End If
End If
Next N
IfMax = Max
End Function
In cells E2:G10 change them to read
=IfMax($A2,$A$2:$A$10,B$2:B$10)
This will refer to the function defined above - providing the three
parameters required by the function.
Let me explain how this function works. The IfMax function is passed
three parameters:
- the cell to match
- the range of cells to match
- the range of cells with the value to get the maximum
Max is a variable used to keep the current "maximum value". Initially
assume the "first" value is the maximum value.
The For loop is used to walk through each value in the range of cells
to match
The first If statement is used to select only those rows where the
match is made.
The second If statement is used to update the Max value if this cell
is larger than the current Max.
IfMin is generated in a similar manner to read
Function IfMin(Selector, Match, Value)
'
' IfMin Macro
' Macro created 5/22/2003 by Maniac
'
'
Min = Value.Cells(1).Value
For N = 1 To Match.Count
If (Match.Cells(N).Value = Selector.Value) Then
If (Min < Value.Cells(N).Value) Then
Min = Value.Cells(N).Value
End If
End If
Next N
IfMin = Max
End Function
This basic structure can be changed to generate averages and other
similar functions. For example, if you have varying amounts of data to
process each day, but the columns remain the same each day it is
possible to process the "whole worksheet" with a slight change. I
would use something like
For N = 1 to ActiveCell.CurrentRegion.Rows.Count
to determine the number of rows of data and adjust the value
references to process each row in the "current region" (a range
bounded by blank rows / columns). If something like this is what you
want - please use a clarification request.
Please use a request clarification request if you have any difficulty
with this answer.
--Maniac
|
,
0 Comments
)