Finding "MAX" number

  • Thread starter Thread starter Rita Palazzi
  • Start date Start date
R

Rita Palazzi

Windows 2000
Office 2000

Hi Folks,
Hopefully someone can help me with a problem. I have a huge spreadsheet that
gathers data every day. There is a date column so each row represents values for
one day. There is also a month column that indicates which month that date falls
into i.e. every day in June will have a 6 for the month.

I have another spreadsheet that summarizes the daily worksheet into months,
usually giving the average daily value for the each month. However, I have one
column that I need to know the maximum value (highest volume) on.

How do I write a function that will look at the column only for one month and tell
me the maximum value for that month?

Thanks in advance for any help you can give.

Rita Palazzi
Engineer / FedEx Express
 
With month # in column A and volume in column B:

=MAX(IF(A1:A100=6,B1:B100))

Array-entered.

HTH
Jason
Atlanta, GA
 
As Jason said, you need to array-enter. That is, use CTRL+SHIFT+ENTER rather
than just ENTER.
 
Ok, that's a new one to me!! I did the array-enter as you described and it worked
perfectly. I have NO CLUE as to what "array-entered" means. Can you tell me what
just happened?!?!?

Thanks to Anon and Jason both for your help!!!
Rita
 
Search for "array formulas" in Help for a full description.

Briefly, Excel calculates formulas from the 'inside', each time passing a
value, or an array of values, 'outwards' to the next function.

For example, in the formula
=MAX(SUM(A1:A10),20)
the SUM is calculated first, and the result (a single value) is passed
'outwards' to the MAX function.
This is a normal formula.

Similarly, in the formula
=MAX(IF(A1:A100=6,B1:B100))
the IF is calculated first, but this time the result to be passed to the MAX
function is an array.
This is an array-formula. Excel has to be told that it is an array-formula
by entering with CTRL+SHIFT+ENTER.
 
THANK YOU, Anon!

I have struggled with when to use Ctrl+Shift+Enter versus plain Enter for
quite some time, and now I think I finally understand!

Thanks!
Steve
 
Back
Top