View Single Post
  #6   Report Post  
Posted to microsoft.public.excel.misc
pinmaster pinmaster is offline
external usenet poster
 
Posts: 347
Default Array Formula Minimum Excluding Text

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

"Adams SC" wrote:



"Adams SC" wrote:

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