Home |
Search |
Today's Posts |
#1
|
|||
|
|||
MIN Function w/ variable address reference
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, |
#2
|
|||
|
|||
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. "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, |
#3
|
|||
|
|||
=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, |
#4
|
|||
|
|||
=MIN(B37:INDEX(B37:AA37,MATCH(2,1/(B37:AA370))))
followed by control+shift+enter. 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. 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, |
#5
|
|||
|
|||
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? "Aladin Akyurek" wrote: =MIN(B37:INDEX(B37:AA37,MATCH(2,1/(B37:AA370)))) followed by control+shift+enter. 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. 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, |
#6
|
|||
|
|||
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, |
#7
|
|||
|
|||
|
#8
|
|||
|
|||
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:AA370,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")) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Construct address as a reference not "text" | Excel Worksheet Functions | |||
How do I obtain the address of a cell using the vlookup function? | Excel Worksheet Functions | |||
return the column reference number of a function result | Excel Discussion (Misc queries) | |||
how do i reference multiple rows/columns with one function? | New Users to Excel | |||
Cell reference - for the sheet name, can I use a variable? | Excel Discussion (Misc queries) |