|
Formula Too Long Issue
How do you fix it if a formula is too long?
|
Formula Too Long Issue
Depends. Post it
-- Don Guillett SalesAid Software "mldancing" wrote in message ... How do you fix it if a formula is too long? |
Formula Too Long Issue
Post the formula so possible solutions can be offered.
"mldancing" wrote: How do you fix it if a formula is too long? |
Formula Too Long Issue
"mldancing" wrote:
How do you fix it if a formula is too long? Try making the cell references in the formula shorter Use short and sweet sheetnames, like: A, B, C or : 1,2,3 or: T1,T2,T3 Keep all sheets in same book. Avoid using links across books. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Formula Too Long Issue
=INDEX(Detail_Pivot!$B$27:$N$50,MATCH("Kimberlee
Madsen",Detail_Pivot!$A$27:$A$50,0),MATCH("Week 10",Detail_Pivot!$B$26:$N$26,0))+INDEX(Detail_Pivo t!$B$27:$N$50,MATCH("Kimberlee Madsen",Detail_Pivot!$A$27:$A$50,0),MATCH("Week 11",Detail_Pivot!$B$26:$N$26,0))+INDEX(Detail_Pivo t!$B$27:$N$50,MATCH("Kimberlee Madsen",Detail_Pivot!$A$27:$A$50,0),MATCH("Week 12",Detail_Pivot!$B$26:$N$26,0))+INDEX(Detail_Pivo t!$B$27:$N$50,MATCH("Kimberlee Madsen",Detail_Pivot!$A$27:$A$50,0),MATCH("Week 13",Detail_Pivot!$B$26:$N$26,0)) I want to include if(isna) in each of the index so that if it's blank, it will read as 0 and will still add up. Thanks for your help. "Don Guillett" wrote: Depends. Post it -- Don Guillett SalesAid Software "mldancing" wrote in message ... How do you fix it if a formula is too long? |
Formula Too Long Issue
I want to include if(isna) in each of the index so that if it's blank
What's blank? The only things that will cause #N/A errors is if Kimberlee Madsen can't be found or the headers Week xx can't be found or you have #N/A errors in the table itself. Need more detail Biff "mldancing" wrote in message ... =INDEX(Detail_Pivot!$B$27:$N$50,MATCH("Kimberlee Madsen",Detail_Pivot!$A$27:$A$50,0),MATCH("Week 10",Detail_Pivot!$B$26:$N$26,0))+INDEX(Detail_Pivo t!$B$27:$N$50,MATCH("Kimberlee Madsen",Detail_Pivot!$A$27:$A$50,0),MATCH("Week 11",Detail_Pivot!$B$26:$N$26,0))+INDEX(Detail_Pivo t!$B$27:$N$50,MATCH("Kimberlee Madsen",Detail_Pivot!$A$27:$A$50,0),MATCH("Week 12",Detail_Pivot!$B$26:$N$26,0))+INDEX(Detail_Pivo t!$B$27:$N$50,MATCH("Kimberlee Madsen",Detail_Pivot!$A$27:$A$50,0),MATCH("Week 13",Detail_Pivot!$B$26:$N$26,0)) I want to include if(isna) in each of the index so that if it's blank, it will read as 0 and will still add up. Thanks for your help. "Don Guillett" wrote: Depends. Post it -- Don Guillett SalesAid Software "mldancing" wrote in message ... How do you fix it if a formula is too long? |
Formula Too Long Issue
From your original formula I have assumed you want 4 weeks worth of data: if
so, try: =SUMPRODUCT((Detail_Pivot!$A$27:$A$50=$A$1)*(OFFSE T(Detail_Pivot!$B$27,0,MATCH($B$1,Detail_Pivot!$B$ 26:$N$26,0)-1,24,4))) where $A$1 has name e.g "Kimberlee Madsen" $B$1 has first week (of the 4) e.g "Week 10" You can add error logic as required. As Max pointed out, you could shorten the formula by using named ranges. HTH "Max" wrote: "mldancing" wrote: How do you fix it if a formula is too long? Try making the cell references in the formula shorter Use short and sweet sheetnames, like: A, B, C or : 1,2,3 or: T1,T2,T3 Keep all sheets in same book. Avoid using links across books. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Formula Too Long Issue
This formula works! Thank you so much HTH. Can you point me to a link where I
can learn more about offset in the formula. I still don't quite get the logic of it. Thanks Max for the pointers too! "Toppers" wrote: From your original formula I have assumed you want 4 weeks worth of data: if so, try: =SUMPRODUCT((Detail_Pivot!$A$27:$A$50=$A$1)*(OFFSE T(Detail_Pivot!$B$27,0,MATCH($B$1,Detail_Pivot!$B$ 26:$N$26,0)-1,24,4))) where $A$1 has name e.g "Kimberlee Madsen" $B$1 has first week (of the 4) e.g "Week 10" You can add error logic as required. As Max pointed out, you could shorten the formula by using named ranges. HTH "Max" wrote: "mldancing" wrote: How do you fix it if a formula is too long? Try making the cell references in the formula shorter Use short and sweet sheetnames, like: A, B, C or : 1,2,3 or: T1,T2,T3 Keep all sheets in same book. Avoid using links across books. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Formula Too Long Issue
Look at HELP in OFFSET function.
To break down my Formula: (OFFSET(Detail_Pivot!$B$27,0,MATCH($B$1,Detail_Piv ot!$B$26:$N$26,0)-1,24,4))) Detail_Pivot!$B$27 == this positions the start (reference cell) at your first data cell 0 == just positions on the row determined by the SUMPRODUCT(Detail_Pivot!$A$27:$A$50=$A$1) i.e. the row matching the name MATCH($B$1,Detail_Pivot!$B$26:$N$26,0)-1== finds the column corresponding to the WEEK and we subtract one as its an offset from column B; if the match is B26, MATCH will return 1 and as our reference cell is B27 we want n offset of 0 ...hence subtract one. 24 == is the number of rows your data occupies (rows 27 to 50) 4 ==is the number of columns we want to sum HTH "mldancing" wrote: This formula works! Thank you so much HTH. Can you point me to a link where I can learn more about offset in the formula. I still don't quite get the logic of it. Thanks Max for the pointers too! "Toppers" wrote: From your original formula I have assumed you want 4 weeks worth of data: if so, try: =SUMPRODUCT((Detail_Pivot!$A$27:$A$50=$A$1)*(OFFSE T(Detail_Pivot!$B$27,0,MATCH($B$1,Detail_Pivot!$B$ 26:$N$26,0)-1,24,4))) where $A$1 has name e.g "Kimberlee Madsen" $B$1 has first week (of the 4) e.g "Week 10" You can add error logic as required. As Max pointed out, you could shorten the formula by using named ranges. HTH "Max" wrote: "mldancing" wrote: How do you fix it if a formula is too long? Try making the cell references in the formula shorter Use short and sweet sheetnames, like: A, B, C or : 1,2,3 or: T1,T2,T3 Keep all sheets in same book. Avoid using links across books. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Formula Too Long Issue
One last question on this:
So with this formula, what should the formula be so that it will return blanks instead of displaying it as 0 (zero)? I tried several ways but couldn't get it to work. Please help again. Thank you. "Toppers" wrote: Look at HELP in OFFSET function. To break down my Formula: (OFFSET(Detail_Pivot!$B$27,0,MATCH($B$1,Detail_Piv ot!$B$26:$N$26,0)-1,24,4))) Detail_Pivot!$B$27 == this positions the start (reference cell) at your first data cell 0 == just positions on the row determined by the SUMPRODUCT(Detail_Pivot!$A$27:$A$50=$A$1) i.e. the row matching the name MATCH($B$1,Detail_Pivot!$B$26:$N$26,0)-1== finds the column corresponding to the WEEK and we subtract one as its an offset from column B; if the match is B26, MATCH will return 1 and as our reference cell is B27 we want n offset of 0 ...hence subtract one. 24 == is the number of rows your data occupies (rows 27 to 50) 4 ==is the number of columns we want to sum HTH "mldancing" wrote: This formula works! Thank you so much HTH. Can you point me to a link where I can learn more about offset in the formula. I still don't quite get the logic of it. Thanks Max for the pointers too! "Toppers" wrote: From your original formula I have assumed you want 4 weeks worth of data: if so, try: =SUMPRODUCT((Detail_Pivot!$A$27:$A$50=$A$1)*(OFFSE T(Detail_Pivot!$B$27,0,MATCH($B$1,Detail_Pivot!$B$ 26:$N$26,0)-1,24,4))) where $A$1 has name e.g "Kimberlee Madsen" $B$1 has first week (of the 4) e.g "Week 10" You can add error logic as required. As Max pointed out, you could shorten the formula by using named ranges. HTH "Max" wrote: "mldancing" wrote: How do you fix it if a formula is too long? Try making the cell references in the formula shorter Use short and sweet sheetnames, like: A, B, C or : 1,2,3 or: T1,T2,T3 Keep all sheets in same book. Avoid using links across books. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Formula Too Long Issue
This ....
=IF(SUMPRODUCT((Detail_Pivot!$A$27:$A$50=$A$1)*(OF FSET(Detail_Pivot!$B$27,0,MATCH($B$1,Detail_Pivot! $B$26:$N$26,0)-1,24,4)))=0,"",SUMPRODUCT((Detail_Pivot!$A$27:$A$5 0=$A$1)*(OFFSET(Detail_Pivot!$B$27,0,MATCH($B$1,De tail_Pivot!$B$26:$N$26,0)-1,24,4)))) Another option: Define a range (Insert=Name=Define==Name: e.g. Myformula =Refers to: ....formula below =SUMPRODUCT((Detail_Pivot!$A$27:$A$50=$A$1)*(OFFSE T(Detail_Pivot!$B$27,0,MATCH($B$1,Detail_Pivot!$B$ 26:$N$26,0)-1,24,4))) Then your long formula becomes =IF(myformula=0,"",myformula) HTH "mldancing" wrote: One last question on this: So with this formula, what should the formula be so that it will return blanks instead of displaying it as 0 (zero)? I tried several ways but couldn't get it to work. Please help again. Thank you. "Toppers" wrote: Look at HELP in OFFSET function. To break down my Formula: (OFFSET(Detail_Pivot!$B$27,0,MATCH($B$1,Detail_Piv ot!$B$26:$N$26,0)-1,24,4))) Detail_Pivot!$B$27 == this positions the start (reference cell) at your first data cell 0 == just positions on the row determined by the SUMPRODUCT(Detail_Pivot!$A$27:$A$50=$A$1) i.e. the row matching the name MATCH($B$1,Detail_Pivot!$B$26:$N$26,0)-1== finds the column corresponding to the WEEK and we subtract one as its an offset from column B; if the match is B26, MATCH will return 1 and as our reference cell is B27 we want n offset of 0 ...hence subtract one. 24 == is the number of rows your data occupies (rows 27 to 50) 4 ==is the number of columns we want to sum HTH "mldancing" wrote: This formula works! Thank you so much HTH. Can you point me to a link where I can learn more about offset in the formula. I still don't quite get the logic of it. Thanks Max for the pointers too! "Toppers" wrote: From your original formula I have assumed you want 4 weeks worth of data: if so, try: =SUMPRODUCT((Detail_Pivot!$A$27:$A$50=$A$1)*(OFFSE T(Detail_Pivot!$B$27,0,MATCH($B$1,Detail_Pivot!$B$ 26:$N$26,0)-1,24,4))) where $A$1 has name e.g "Kimberlee Madsen" $B$1 has first week (of the 4) e.g "Week 10" You can add error logic as required. As Max pointed out, you could shorten the formula by using named ranges. HTH "Max" wrote: "mldancing" wrote: How do you fix it if a formula is too long? Try making the cell references in the formula shorter Use short and sweet sheetnames, like: A, B, C or : 1,2,3 or: T1,T2,T3 Keep all sheets in same book. Avoid using links across books. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Formula Too Long Issue
I did try this route but it didn't work. Still return 0.
"Toppers" wrote: This .... =IF(SUMPRODUCT((Detail_Pivot!$A$27:$A$50=$A$1)*(OF FSET(Detail_Pivot!$B$27,0,MATCH($B$1,Detail_Pivot! $B$26:$N$26,0)-1,24,4)))=0,"",SUMPRODUCT((Detail_Pivot!$A$27:$A$5 0=$A$1)*(OFFSET(Detail_Pivot!$B$27,0,MATCH($B$1,De tail_Pivot!$B$26:$N$26,0)-1,24,4)))) Another option: Define a range (Insert=Name=Define==Name: e.g. Myformula =Refers to: ...formula below =SUMPRODUCT((Detail_Pivot!$A$27:$A$50=$A$1)*(OFFSE T(Detail_Pivot!$B$27,0,MATCH($B$1,Detail_Pivot!$B$ 26:$N$26,0)-1,24,4))) Then your long formula becomes =IF(myformula=0,"",myformula) HTH "mldancing" wrote: One last question on this: So with this formula, what should the formula be so that it will return blanks instead of displaying it as 0 (zero)? I tried several ways but couldn't get it to work. Please help again. Thank you. "Toppers" wrote: Look at HELP in OFFSET function. To break down my Formula: (OFFSET(Detail_Pivot!$B$27,0,MATCH($B$1,Detail_Piv ot!$B$26:$N$26,0)-1,24,4))) Detail_Pivot!$B$27 == this positions the start (reference cell) at your first data cell 0 == just positions on the row determined by the SUMPRODUCT(Detail_Pivot!$A$27:$A$50=$A$1) i.e. the row matching the name MATCH($B$1,Detail_Pivot!$B$26:$N$26,0)-1== finds the column corresponding to the WEEK and we subtract one as its an offset from column B; if the match is B26, MATCH will return 1 and as our reference cell is B27 we want n offset of 0 ...hence subtract one. 24 == is the number of rows your data occupies (rows 27 to 50) 4 ==is the number of columns we want to sum HTH "mldancing" wrote: This formula works! Thank you so much HTH. Can you point me to a link where I can learn more about offset in the formula. I still don't quite get the logic of it. Thanks Max for the pointers too! "Toppers" wrote: From your original formula I have assumed you want 4 weeks worth of data: if so, try: =SUMPRODUCT((Detail_Pivot!$A$27:$A$50=$A$1)*(OFFSE T(Detail_Pivot!$B$27,0,MATCH($B$1,Detail_Pivot!$B$ 26:$N$26,0)-1,24,4))) where $A$1 has name e.g "Kimberlee Madsen" $B$1 has first week (of the 4) e.g "Week 10" You can add error logic as required. As Max pointed out, you could shorten the formula by using named ranges. HTH "Max" wrote: "mldancing" wrote: How do you fix it if a formula is too long? Try making the cell references in the formula shorter Use short and sweet sheetnames, like: A, B, C or : 1,2,3 or: T1,T2,T3 Keep all sheets in same book. Avoid using links across books. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Formula Too Long Issue
It worked OK when I tested it.
If you want send w/sheet to toppers at NOSPAMjohntopley.fsnet.co.uk (remove NOSPAM!) "mldancing" wrote: I did try this route but it didn't work. Still return 0. "Toppers" wrote: This .... =IF(SUMPRODUCT((Detail_Pivot!$A$27:$A$50=$A$1)*(OF FSET(Detail_Pivot!$B$27,0,MATCH($B$1,Detail_Pivot! $B$26:$N$26,0)-1,24,4)))=0,"",SUMPRODUCT((Detail_Pivot!$A$27:$A$5 0=$A$1)*(OFFSET(Detail_Pivot!$B$27,0,MATCH($B$1,De tail_Pivot!$B$26:$N$26,0)-1,24,4)))) Another option: Define a range (Insert=Name=Define==Name: e.g. Myformula =Refers to: ...formula below =SUMPRODUCT((Detail_Pivot!$A$27:$A$50=$A$1)*(OFFSE T(Detail_Pivot!$B$27,0,MATCH($B$1,Detail_Pivot!$B$ 26:$N$26,0)-1,24,4))) Then your long formula becomes =IF(myformula=0,"",myformula) HTH "mldancing" wrote: One last question on this: So with this formula, what should the formula be so that it will return blanks instead of displaying it as 0 (zero)? I tried several ways but couldn't get it to work. Please help again. Thank you. "Toppers" wrote: Look at HELP in OFFSET function. To break down my Formula: (OFFSET(Detail_Pivot!$B$27,0,MATCH($B$1,Detail_Piv ot!$B$26:$N$26,0)-1,24,4))) Detail_Pivot!$B$27 == this positions the start (reference cell) at your first data cell 0 == just positions on the row determined by the SUMPRODUCT(Detail_Pivot!$A$27:$A$50=$A$1) i.e. the row matching the name MATCH($B$1,Detail_Pivot!$B$26:$N$26,0)-1== finds the column corresponding to the WEEK and we subtract one as its an offset from column B; if the match is B26, MATCH will return 1 and as our reference cell is B27 we want n offset of 0 ...hence subtract one. 24 == is the number of rows your data occupies (rows 27 to 50) 4 ==is the number of columns we want to sum HTH "mldancing" wrote: This formula works! Thank you so much HTH. Can you point me to a link where I can learn more about offset in the formula. I still don't quite get the logic of it. Thanks Max for the pointers too! "Toppers" wrote: From your original formula I have assumed you want 4 weeks worth of data: if so, try: =SUMPRODUCT((Detail_Pivot!$A$27:$A$50=$A$1)*(OFFSE T(Detail_Pivot!$B$27,0,MATCH($B$1,Detail_Pivot!$B$ 26:$N$26,0)-1,24,4))) where $A$1 has name e.g "Kimberlee Madsen" $B$1 has first week (of the 4) e.g "Week 10" You can add error logic as required. As Max pointed out, you could shorten the formula by using named ranges. HTH "Max" wrote: "mldancing" wrote: How do you fix it if a formula is too long? Try making the cell references in the formula shorter Use short and sweet sheetnames, like: A, B, C or : 1,2,3 or: T1,T2,T3 Keep all sheets in same book. Avoid using links across books. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
| All times are GMT +1. The time now is 07:38 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com