=MIN(IF(B37:AA37="","",B37:AA37))

--

Vasant


"WLMPilot" wrote in message
...
I have created a spreadsheet that duplicates my paystub. With this
spreadsheet, I track various data fields, ie average net pay, average
gross
pay, etc. All dollar fields are formatted to "Accounting". I am trying
to
do a MIN/MAX on various fields. Unfortunately, if I simply type
MIN(B37:AA37), all 26 payperiods, then the result is "$ - ", which is
the display for a zero value in an "account" formatted field. I am trying
to
create a moving cell reference so that the MIN function will not pick up
the
unused payperiods (columns). Below is the formula that I thought would
work,
but it keeps saying there is an error and I don't know what the error is:

=MIN(B37:ADDRESS(37,COUNTIF(B37:AA37,"0")+1,4))

A breakdown of the above formula is as follows:

The COUNTIF(B37:AA37,"0") command looks at 26 columns (payperiods) and
counts the total number of columns that have an amount greater than zero.
The result can also represent number of payperiods that have passed so
far.

The "+1" is to adjust the count up one since the first payperiod is in
column 2 vs
column 1. If we just had the first payperiod, then the "+1" forces the
reference in the ADDRESS command to column 2, ie "B"

The "4" in the ADDRESS command simply makes the reference relative. This
may or may not be needed.

The "37" in the ADDRESS command simply references the row.

In summary, the "=MIN(B37:" is the only fixed part of the formula. As
each
paystub is entered, the ADDRESS portion should advance one column, thus
changing the cell reference from B37, to C37, D37, E37....AA37 as each
payperiod is entered and the value in the array is greater than zero.

The =MAX(B37:AA37) works fine in this setting since the unused payperiods
equal zero and I am looking for the max. amount. However, I need to block
out the fields that have a zero value in the MIN command in order to get
what
I want.

Any help is greatly appreciated,