Sumproduct with Range Names

[soteman2005]

Hi,

I have been using range names for each row in my worksheet and now I am
trying to use sumproduct but I can't get it to work using the range
names as it takes the entire range, not just the current column. I
have rangenamed by columns as well so I tried using those to define it
but with no success...any help would be great, maybe a UDF could do
it??

 

[Guest]

I use that quite often
but you must ensure that there is only one cell ( if that is what you need)
in the range available to use in the formula.
Ie.
ColumnA B C D
point1.x point1.y point1.z

Row1 is NamedRange: NOMINAL 1
Row2 is NamedRange: UPPER 0.5
Row3 is NamedRange: LOWER -0.5
Row4 is NamedRange: ABS ABS(F47)
Row5 is NamedRange: LSL SUM(NOMINAL-ABS)
Row6 is NamedRange: USL SUM(NOMINAL+F46)
Row7 is NamedRange: MIN MIN(F72:F542)
Row8 is NamedRange: MAX MAX(F72:F542)
Row9 is NamedRange: RANGE SUM(MAX-MIN)
Row10 is NamedRange: AVE AVERAGE(F72:F542)
Row11 is NamedRange: MEDIAN MEDIAN(F72:F542)
Row12 is NamedRange: AVE-NOM SUM(AVE-NOMINAL)
Row13 is NamedRange: Stdev STDEV(F72:F542)
Row14 is NamedRange:
CPK: IF(USL-AVE>AVE-LSL,(AVE-LSL)/(Stdev*3),(LSL-AVE)/(Stdev*3))


CPK would be in row14 in column B, C, D…etc and pull the Named Range from
that column ( Any Named Range occurs only once every column)
HTH

 

[Niek Otten]

Use the space as the intersection operator. Type the defined name, a space
and then click the column header. This will give you only the "current"
column.

--
Kind regards,

Niek Otten

"soteman2005" <[email protected]>
wrote in message
news:[email protected]...