Array Formula Minimum Excluding Text

[Guest]

I have a group # in column A. In column G, for each group, I want to take
value in column F - Less the minimum for group. I can get this far, in some
cases I don't have a value in column F, I have a text entry. I want those
entries ignored for the minimum calculation.

 

[Guest]

The MIN function already ignores text values...

Dave

 

[Ron Rosenfeld]

On Wed, 7 Mar 2007 13:01:10 -0800, Adams SC <Adams

I have a group # in column A. In column G, for each group, I want to take
value in column F - Less the minimum for group. I can get this far, in some
cases I don't have a value in column F, I have a text entry. I want those
entries ignored for the minimum calculation.

If you enter the argument as a range, MIN will ignore text
--ron

 

[Guest]

Hi,

Not sure I understand but perhaps something like this:

=IF(ISTEXT(F1),"",F1-MIN(IF($A$1:$A$10=A1,$F$1:$F$10)))
enter using Ctrl+Shift+Enter then copy down

HTH
Jean-Guy

 

[Guest]

I have a group # in column A. In column G, for each group, I want to take
value in column F - Less the minimum for group. I can get this far, in some
cases I don't have a value in column F, I have a text entry. I want those
entries ignored for the minimum calculation.

More Information
Columns

A D E F G
1 0.58 -0.006 0.574 0.006
1 0.62 -0.006 0.614 0.046
1 0.59 -0.022 0.568 -
1 0.61 -0.006 0.604 -
2 0.57 -0.006 0.564 -
2 0.58 -0.006 0.574 0.006
2 0.59 -0.022 0.568 -
2 0.61 -0.006 0.604 -
3 0.645 -0.023 0.622 -
3 Price n/a Price n/a Price n/a #VALUE!
3 0.64 -0.006 0.634 -


Formula in G currently is =F2-MIN(IF(A2:A47=Table3[[#This
Row],[Group]],F2:F47))

 

[Guest]

Hi,

This should do:

=IF(ISTEXT(F2),F2,F2-MIN(IF($A$2:$A$47=A2,$F$2:$F$47)))
enter using Ctrl+Shift+Enter

Note: if you don't make your ranges absolute it will give false results like
in your sample data. The values in the last column are the results of my
formula.

1 0.58 -0.006 0.574 0.006 0.006
1 0.62 -0.006 0.614 0.046 0.046
1 0.59 -0.022 0.568 - 0
1 0.61 -0.006 0.604 - 0.036
2 0.57 -0.006 0.564 - 0
2 0.58 -0.006 0.574 0.006 0.01
2 0.59 -0.022 0.568 - 0.004
2 0.61 -0.006 0.604 - 0.04
3 0.645 -0.023 0.622 - 0
3 Price n/a Price n/a Price n/a #VALUE! Price n/a
3 0.64 -0.006 0.634 - 0.012

HTH
Jean-Guy

I have a group # in column A. In column G, for each group, I want to take
value in column F - Less the minimum for group. I can get this far, in some
cases I don't have a value in column F, I have a text entry. I want those
entries ignored for the minimum calculation.

More Information
Columns

A D E F G
1 0.58 -0.006 0.574 0.006
1 0.62 -0.006 0.614 0.046
1 0.59 -0.022 0.568 -
1 0.61 -0.006 0.604 -
2 0.57 -0.006 0.564 -
2 0.58 -0.006 0.574 0.006
2 0.59 -0.022 0.568 -
2 0.61 -0.006 0.604 -
3 0.645 -0.023 0.622 -
3 Price n/a Price n/a Price n/a #VALUE!
3 0.64 -0.006 0.634 -


Formula in G currently is =F2-MIN(IF(A2:A47=Table3[[#This
Row],[Group]],F2:F47))