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

--

Vasant


"WLMPilot" <WLMPilot@discussions.microsoft.com> wrote in message
news:73C9BFE2-126B-4007-8ADA-13B23F95F62F@microsoft.com...
> 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,
>
>
>