MIN Function w/ variable address reference

[WLMPilot]

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,

 

[Dave R.]

Try using the INDIRECT function when referencing this range, i.e.

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

Haven't checked over the rest of your formula to see if it will work, but at
the very least you need indirect to reference a range like that.

 

[Vasant Nanavati]

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

 

[Aladin Akyurek]

=MIN(B37:INDEX(B37:AA37,MATCH(2,1/(B37:AA37>0))))

followed by control+shift+enter.

 

[WLMPilot]

This formulas did not work. I entered it like you said and the cell remained
empty, ie no value result. The formula was in the cell when I clicked on the
cell. I would like to know the purpose of using CNTRL-SHIFT-ENTER?

 

[WLMPilot]

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.

 

[WLMPilot]

Dave,

I apologize for incorrectly responding that this formula did not work.
Initially it did not, and I don't know why, BUT IT DOES WORK. Thank you!!!

Would you please email me at (e-mail address removed) with the breakdown of this
formula. I am trying to understand it. Everything from "ADDRESS.......", I
understand since it was part of my original formula. I would like to
understand the function INDIRECT and why quotes were needed and the "&" was
needed. I also am curious why the beginning cell reference of B37 did not
appear before the INDIRECT command, ie MIN(B37:INDIRECT.........).

Thanks again for your help
Les

 

[Harlan Grove]

WLMPilot wrote...

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. . . .

So you're using zeros rather than "" for pay periods not yet entered?
Bad design. If you used "" to represent unentered values instead, you
could use MIN as-is since it ignores cells not evaluating to numbers.

. . . 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))

....

This doesn't work because ADDRESS returns a text string, not a range
reference. That is, the ADDRESS call would return something like "V37",
not V37. That would reduce your formula to

=MIN(B37:"V37")

and that's a syntax error. If you enclose the ADDRESS call in an
INDIRECT call, the "V37" effectively becomes V37, a range reference. So
if

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

returns an error, it'd be due to the COUNTIF call returning something
invalid.

Alternatives: if you want to exclude pay periods in which the values
are zero, use the *ARRAY* formula

=MIN(IF(B37:AA37>0,B37:AA37))

[Enter array formulas using the [Ctrl]+[Shift]+[Enter] key
combination.] Or use a variation on your original formula

=MIN(OFFSET(B37,0,0,COUNTIF(B37:AA37,">0"),1))

or get tricky

=LARGE(B37:AA37,COUNTIF(B37:AA37,">0"))