Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sumproduct with Range Names
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?? -- soteman2005 ------------------------------------------------------------------------ soteman2005's Profile: http://www.excelforum.com/member.php...o&userid=29078 View this thread: http://www.excelforum.com/showthread...hreadid=510528 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sumproduct with Range Names
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-AVEAVE-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 "soteman2005" wrote: 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?? -- soteman2005 ------------------------------------------------------------------------ soteman2005's Profile: http://www.excelforum.com/member.php...o&userid=29078 View this thread: http://www.excelforum.com/showthread...hreadid=510528 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sumproduct with Range Names
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" wrote in message ... 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?? -- soteman2005 ------------------------------------------------------------------------ soteman2005's Profile: http://www.excelforum.com/member.php...o&userid=29078 View this thread: http://www.excelforum.com/showthread...hreadid=510528 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
converting email address names in a range of cells to real names | Excel Worksheet Functions | |||
COPYING FORMULA CONTAINING NAMES/RELATIVE RANGE NAMES | Excel Discussion (Misc queries) | |||
Using NAMES in SUMPRODUCT giving error | Excel Worksheet Functions | |||
Selecting range in list of range names depending on a cell informa | Excel Discussion (Misc queries) | |||
Dynamic range names, multiple criteria, sumproduct | Excel Discussion (Misc queries) |