This formula returned the #VALUE result. If I understand this formula, it is
going to look at the array B37:AA37. If it is null anywher in the array,
then the result will be null, otherwise it will find the minimum of B37:AA37.


As stated, it did not work and I believe the formula would need to be
entered differently to try to do what I think you were trying to do.
Basically I need to create a MIN function with a fixed and a floating cell
range. Columns B37 - AA37 represent a payperiod, 26 total in my case.
Currently, we have only had 11 pay periods, which means the remaining pay
periods (I37 - AA37) have a zero value with an "ACCOUNTING" format. The
"end" cell reference in the MIN function needs to adjust as each payperiod
passes. Therefore the first pay period should result in the MIN function
being MIN(B37:value), where "value" = B37. The second payperiod, "value"
would equal C37, then D37, E37, etc., as each pay period passed.

Thanks for your input and if you come up with anything else, please let me
know.

"Vasant Nanavati" wrote:

=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,