| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct error caused by too many curly brackets?
Hi, HAVING ERROR MESSAGES WITH THE FOLLOWING FORMULA.
iF i USE A SINGLE VARIABLE IN COLLUMN fp SUCH AS "w", THEN ALL IS FINE. iT CAN`T SEEM TO RETRIEVE 4 DIFFERENT VALUES WHILST ALSO LOOKING ACROSS FOR THE RANGE OF VALUES IN COLLUMN ge. aNY IDEAS ???? tHANKS rr1 =SUMPRODUCT(($FP$10:$FP$89={"W","SA","SA+","ST"})* ($GE$10:$GE$89={"4c","4c+","4b","4b+","4a","4a+"," 5c","5c+","5b","5b+","5a","5a+","6c"}))/SUMPRODUCT(($FP$10:$FP$89={"W","SA","SA+","ST"})*( $GE$10:$GE$890))) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct error caused by too many curly brackets?
Try the below
=SUMPRODUCT( ($FP$10:$FP$89={"W","SA","SA+","ST"})* ($GE$10:$GE$89={"4c","4c+","4b","4b+","4a","4a+"," 5c","5c+","5b","5b+","5a","5a+","6c"})) /SUMPRODUCT(($FP$10:$FP$89={"W","SA","SA+","ST"})*( $GE$10:$GE$890)) If this post helps click Yes --------------- Jacob Skaria "Romileyrunner1" wrote: Hi, HAVING ERROR MESSAGES WITH THE FOLLOWING FORMULA. iF i USE A SINGLE VARIABLE IN COLLUMN fp SUCH AS "w", THEN ALL IS FINE. iT CAN`T SEEM TO RETRIEVE 4 DIFFERENT VALUES WHILST ALSO LOOKING ACROSS FOR THE RANGE OF VALUES IN COLLUMN ge. aNY IDEAS ???? tHANKS rr1 =SUMPRODUCT(($FP$10:$FP$89={"W","SA","SA+","ST"})* ($GE$10:$GE$89={"4c","4c+","4b","4b+","4a","4a+"," 5c","5c+","5b","5b+","5a","5a+","6c"}))/SUMPRODUCT(($FP$10:$FP$89={"W","SA","SA+","ST"})*( $GE$10:$GE$890))) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct error caused by too many curly brackets?
tried this thanks, but still have the same errors.
any further suggestions? rr1 thanks. "Jacob Skaria" wrote: Try the below =SUMPRODUCT( ($FP$10:$FP$89={"W","SA","SA+","ST"})* ($GE$10:$GE$89={"4c","4c+","4b","4b+","4a","4a+"," 5c","5c+","5b","5b+","5a","5a+","6c"})) /SUMPRODUCT(($FP$10:$FP$89={"W","SA","SA+","ST"})*( $GE$10:$GE$890)) If this post helps click Yes --------------- Jacob Skaria "Romileyrunner1" wrote: Hi, HAVING ERROR MESSAGES WITH THE FOLLOWING FORMULA. iF i USE A SINGLE VARIABLE IN COLLUMN fp SUCH AS "w", THEN ALL IS FINE. iT CAN`T SEEM TO RETRIEVE 4 DIFFERENT VALUES WHILST ALSO LOOKING ACROSS FOR THE RANGE OF VALUES IN COLLUMN ge. aNY IDEAS ???? tHANKS rr1 =SUMPRODUCT(($FP$10:$FP$89={"W","SA","SA+","ST"})* ($GE$10:$GE$89={"4c","4c+","4b","4b+","4a","4a+"," 5c","5c+","5b","5b+","5a","5a+","6c"}))/SUMPRODUCT(($FP$10:$FP$89={"W","SA","SA+","ST"})*( $GE$10:$GE$890))) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct error caused by too many curly brackets?
=SUMPRODUCT(
($FP$10:$FP$89={"W","SA","SA+","ST"})* (isnumber(match($GE$10:$GE$89,{"4c","4c+","4b","4b +","4a","4a+","5c","5c+","5b","5b+","5a","5a+","6c "},0)))) /SUMPRODUCT(($FP$10:$FP$89={"W","SA","SA+","ST"})*( $GE$10:$GE$890)) "Romileyrunner1" wrote: Hi, HAVING ERROR MESSAGES WITH THE FOLLOWING FORMULA. iF i USE A SINGLE VARIABLE IN COLLUMN fp SUCH AS "w", THEN ALL IS FINE. iT CAN`T SEEM TO RETRIEVE 4 DIFFERENT VALUES WHILST ALSO LOOKING ACROSS FOR THE RANGE OF VALUES IN COLLUMN ge. aNY IDEAS ???? tHANKS rr1 =SUMPRODUCT(($FP$10:$FP$89={"W","SA","SA+","ST"})* ($GE$10:$GE$89={"4c","4c+","4b","4b+","4a","4a+"," 5c","5c+","5b","5b+","5a","5a+","6c"}))/SUMPRODUCT(($FP$10:$FP$89={"W","SA","SA+","ST"})*( $GE$10:$GE$890))) |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct error caused by too many curly brackets?
Many Thanks Teethless Mama:
works just fine now. Good on ya. RR1 "Teethless mama" wrote: =SUMPRODUCT( ($FP$10:$FP$89={"W","SA","SA+","ST"})* (isnumber(match($GE$10:$GE$89,{"4c","4c+","4b","4b +","4a","4a+","5c","5c+","5b","5b+","5a","5a+","6c "},0)))) /SUMPRODUCT(($FP$10:$FP$89={"W","SA","SA+","ST"})*( $GE$10:$GE$890)) "Romileyrunner1" wrote: Hi, HAVING ERROR MESSAGES WITH THE FOLLOWING FORMULA. iF i USE A SINGLE VARIABLE IN COLLUMN fp SUCH AS "w", THEN ALL IS FINE. iT CAN`T SEEM TO RETRIEVE 4 DIFFERENT VALUES WHILST ALSO LOOKING ACROSS FOR THE RANGE OF VALUES IN COLLUMN ge. aNY IDEAS ???? tHANKS rr1 =SUMPRODUCT(($FP$10:$FP$89={"W","SA","SA+","ST"})* ($GE$10:$GE$89={"4c","4c+","4b","4b+","4a","4a+"," 5c","5c+","5b","5b+","5a","5a+","6c"}))/SUMPRODUCT(($FP$10:$FP$89={"W","SA","SA+","ST"})*( $GE$10:$GE$890))) |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct error caused by too many curly brackets?
Hi,
Try this =sumproduct((isnumber(match($FP$10:$FP$89,A2:A5,0) )*(isnumber(match($GE$10:$GE$89,B2:B14,0))))/sumproduct(isnumber(match($FP$10:$FP$89,A2:A5,0))* ($GE$10:$GE$890)) A2:A5 holds W, S, SA+ and ST. B2:B14 holds 4c, 4c+ etc. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Romileyrunner1" wrote in message ... Hi, HAVING ERROR MESSAGES WITH THE FOLLOWING FORMULA. iF i USE A SINGLE VARIABLE IN COLLUMN fp SUCH AS "w", THEN ALL IS FINE. iT CAN`T SEEM TO RETRIEVE 4 DIFFERENT VALUES WHILST ALSO LOOKING ACROSS FOR THE RANGE OF VALUES IN COLLUMN ge. aNY IDEAS ???? tHANKS rr1 =SUMPRODUCT(($FP$10:$FP$89={"W","SA","SA+","ST"})* ($GE$10:$GE$89={"4c","4c+","4b","4b+","4a","4a+"," 5c","5c+","5b","5b+","5a","5a+","6c"}))/SUMPRODUCT(($FP$10:$FP$89={"W","SA","SA+","ST"})*( $GE$10:$GE$890))) |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct error caused by too many curly brackets?
Great Work Ashish: works fine now: never used "(isnumber(match ..." before
Many Thanks RR1 "Ashish Mathur" wrote: Hi, Try this =sumproduct((isnumber(match($FP$10:$FP$89,A2:A5,0) )*(isnumber(match($GE$10:$GE$89,B2:B14,0))))/sumproduct(isnumber(match($FP$10:$FP$89,A2:A5,0))* ($GE$10:$GE$890)) A2:A5 holds W, S, SA+ and ST. B2:B14 holds 4c, 4c+ etc. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Romileyrunner1" wrote in message ... Hi, HAVING ERROR MESSAGES WITH THE FOLLOWING FORMULA. iF i USE A SINGLE VARIABLE IN COLLUMN fp SUCH AS "w", THEN ALL IS FINE. iT CAN`T SEEM TO RETRIEVE 4 DIFFERENT VALUES WHILST ALSO LOOKING ACROSS FOR THE RANGE OF VALUES IN COLLUMN ge. aNY IDEAS ???? tHANKS rr1 =SUMPRODUCT(($FP$10:$FP$89={"W","SA","SA+","ST"})* ($GE$10:$GE$89={"4c","4c+","4b","4b+","4a","4a+"," 5c","5c+","5b","5b+","5a","5a+","6c"}))/SUMPRODUCT(($FP$10:$FP$89={"W","SA","SA+","ST"})*( $GE$10:$GE$890))) |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct error caused by too many curly brackets?
You are welcome
-- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Romileyrunner1" wrote in message ... Great Work Ashish: works fine now: never used "(isnumber(match ..." before Many Thanks RR1 "Ashish Mathur" wrote: Hi, Try this =sumproduct((isnumber(match($FP$10:$FP$89,A2:A5,0) )*(isnumber(match($GE$10:$GE$89,B2:B14,0))))/sumproduct(isnumber(match($FP$10:$FP$89,A2:A5,0))* ($GE$10:$GE$890)) A2:A5 holds W, S, SA+ and ST. B2:B14 holds 4c, 4c+ etc. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Romileyrunner1" wrote in message ... Hi, HAVING ERROR MESSAGES WITH THE FOLLOWING FORMULA. iF i USE A SINGLE VARIABLE IN COLLUMN fp SUCH AS "w", THEN ALL IS FINE. iT CAN`T SEEM TO RETRIEVE 4 DIFFERENT VALUES WHILST ALSO LOOKING ACROSS FOR THE RANGE OF VALUES IN COLLUMN ge. aNY IDEAS ???? tHANKS rr1 =SUMPRODUCT(($FP$10:$FP$89={"W","SA","SA+","ST"})* ($GE$10:$GE$89={"4c","4c+","4b","4b+","4a","4a+"," 5c","5c+","5b","5b+","5a","5a+","6c"}))/SUMPRODUCT(($FP$10:$FP$89={"W","SA","SA+","ST"})*( $GE$10:$GE$890))) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Inserting curly brackets within a formula | Excel Worksheet Functions | |||
| Error caused by *.xls file | Excel Discussion (Misc queries) | |||
| Getting rid of curly brackets within formula | Excel Worksheet Functions | |||
| Getting rid of curly brackets within formula | Excel Worksheet Functions | |||
| Getting rid of curly brackets within formula | Excel Worksheet Functions | |||
Hybrid Mode
