Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
In A1:C2 I have:
CC Method1 Method2 01016000 4,263.00 4,263.00 Cell B2 contains: =SUMPRODUCT((LookUpTable!$B$7:$B$12=Summary!A2)*(L ookUpTable!$C$7:$G$12)) Cell C2 contains: {=SUM((LookUpTable!$B$7:$B$12=Summary!A2)*LookUpTa ble!$C$7:$G$12)} Both Methods work fine; I'd just like to have another 1 or 2 methods of obtaining the same answer. Any suggestions? Here is my $B$7:$B$12 on LookupTable: 01016000 3,245.00 43.00 432.00 543.00 01016100 456.00 567.00 543.00 01016200 654.00 654.00 01016300 654.00 543.00 567.00 01016400 3,458.00 456.00 7,323.00 01016500 6,788.00 567.00 Thanks in Advance.. |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Here's one but it's not any better than what you have already. In fact, I'd
never use it! But here it is: =SUM(OFFSET(LookUpTable!C$7,MATCH(Summary!A$2,Look UpTable!B$7:B$12,0)-1,,,5)) Biff "JMay" wrote in message ... In A1:C2 I have: CC Method1 Method2 01016000 4,263.00 4,263.00 Cell B2 contains: =SUMPRODUCT((LookUpTable!$B$7:$B$12=Summary!A2)*(L ookUpTable!$C$7:$G$12)) Cell C2 contains: {=SUM((LookUpTable!$B$7:$B$12=Summary!A2)*LookUpTa ble!$C$7:$G$12)} Both Methods work fine; I'd just like to have another 1 or 2 methods of obtaining the same answer. Any suggestions? Here is my $B$7:$B$12 on LookupTable: 01016000 3,245.00 43.00 432.00 543.00 01016100 456.00 567.00 543.00 01016200 654.00 654.00 01016300 654.00 543.00 567.00 01016400 3,458.00 456.00 7,323.00 01016500 6,788.00 567.00 Thanks in Advance.. |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Here's another one. I might even use this one over the Sumproduct version:
Defined names: Table Refers to: LookUpTable!$C$7:$G$12 ID Refers to: LookUpTable!$B$7:$B$12 =SUM(INDEX(Table,MATCH(Summary!A2,ID,0),)) Biff "T. Valko" wrote in message ... Here's one but it's not any better than what you have already. In fact, I'd never use it! But here it is: =SUM(OFFSET(LookUpTable!C$7,MATCH(Summary!A$2,Look UpTable!B$7:B$12,0)-1,,,5)) Biff "JMay" wrote in message ... In A1:C2 I have: CC Method1 Method2 01016000 4,263.00 4,263.00 Cell B2 contains: =SUMPRODUCT((LookUpTable!$B$7:$B$12=Summary!A2)*(L ookUpTable!$C$7:$G$12)) Cell C2 contains: {=SUM((LookUpTable!$B$7:$B$12=Summary!A2)*LookUpTa ble!$C$7:$G$12)} Both Methods work fine; I'd just like to have another 1 or 2 methods of obtaining the same answer. Any suggestions? Here is my $B$7:$B$12 on LookupTable: 01016000 3,245.00 43.00 432.00 543.00 01016100 456.00 567.00 543.00 01016200 654.00 654.00 01016300 654.00 543.00 567.00 01016400 3,458.00 456.00 7,323.00 01016500 6,788.00 567.00 Thanks in Advance.. |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Hi Jim
If you used another helper column on your lookup table, say H7:H12 with the formula =SUM(C7:G12) then the simple =SUMIF(LookUpTable!$B$7:$B$12,Summary!A2,LookUpTab le!$H$7:$H$12) On the range sizes used, speed difference would not be noticed, but on larger arrays, it would certainly be faster than the array formula, and probably faster than the Sumproduct solution. -- Regards Roger Govier "JMay" wrote in message ... In A1:C2 I have: CC Method1 Method2 01016000 4,263.00 4,263.00 Cell B2 contains: =SUMPRODUCT((LookUpTable!$B$7:$B$12=Summary!A2)*(L ookUpTable!$C$7:$G$12)) Cell C2 contains: {=SUM((LookUpTable!$B$7:$B$12=Summary!A2)*LookUpTa ble!$C$7:$G$12)} Both Methods work fine; I'd just like to have another 1 or 2 methods of obtaining the same answer. Any suggestions? Here is my $B$7:$B$12 on LookupTable: 01016000 3,245.00 43.00 432.00 543.00 01016100 456.00 567.00 543.00 01016200 654.00 654.00 01016300 654.00 543.00 567.00 01016400 3,458.00 456.00 7,323.00 01016500 6,788.00 567.00 Thanks in Advance.. |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Thanks Biff;
Both are Cool,, Jim "T. Valko" wrote in message : Here's another one. I might even use this one over the Sumproduct version: Defined names: Table Refers to: LookUpTable!$C$7:$G$12 ID Refers to: LookUpTable!$B$7:$B$12 =SUM(INDEX(Table,MATCH(Summary!A2,ID,0),)) Biff "T. Valko" wrote in message ... Here's one but it's not any better than what you have already. In fact, I'd never use it! But here it is: =SUM(OFFSET(LookUpTable!C$7,MATCH(Summary!A$2,Look UpTable!B$7:B$12,0)-1,,,5)) Biff "JMay" wrote in message ... In A1:C2 I have: CC Method1 Method2 01016000 4,263.00 4,263.00 Cell B2 contains: =SUMPRODUCT((LookUpTable!$B$7:$B$12=Summary!A2)*(L ookUpTable!$C$7:$G$12)) Cell C2 contains: {=SUM((LookUpTable!$B$7:$B$12=Summary!A2)*LookUpTa ble!$C$7:$G$12)} Both Methods work fine; I'd just like to have another 1 or 2 methods of obtaining the same answer. Any suggestions? Here is my $B$7:$B$12 on LookupTable: 01016000 3,245.00 43.00 432.00 543.00 01016100 456.00 567.00 543.00 01016200 654.00 654.00 01016300 654.00 543.00 567.00 01016400 3,458.00 456.00 7,323.00 01016500 6,788.00 567.00 Thanks in Advance.. |
#6
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Thanks Roger
I was trying to avoid the helper-column "thing"; Good to keep in mind the speed factor variances, however. Thanks, Jim "Roger Govier" wrote in message : Hi Jim If you used another helper column on your lookup table, say H7:H12 with the formula =SUM(C7:G12) then the simple =SUMIF(LookUpTable!$B$7:$B$12,Summary!A2,LookUpTab le!$H$7:$H$12) On the range sizes used, speed difference would not be noticed, but on larger arrays, it would certainly be faster than the array formula, and probably faster than the Sumproduct solution. -- Regards Roger Govier "JMay" wrote in message ... In A1:C2 I have: CC Method1 Method2 01016000 4,263.00 4,263.00 Cell B2 contains: =SUMPRODUCT((LookUpTable!$B$7:$B$12=Summary!A2)*(L ookUpTable!$C$7:$G$12)) Cell C2 contains: {=SUM((LookUpTable!$B$7:$B$12=Summary!A2)*LookUpTa ble!$C$7:$G$12)} Both Methods work fine; I'd just like to have another 1 or 2 methods of obtaining the same answer. Any suggestions? Here is my $B$7:$B$12 on LookupTable: 01016000 3,245.00 43.00 432.00 543.00 01016100 456.00 567.00 543.00 01016200 654.00 654.00 01016300 654.00 543.00 567.00 01016400 3,458.00 456.00 7,323.00 01016500 6,788.00 567.00 Thanks in Advance.. |
#7
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Hi Jim
I was trying to avoid the helper-column "thing" I hear what you say, and I respect your opinion. I do find it strange though, how averse people are to using extra or "helper" columns. Whilst I often post answers to people using perhaps long or complicated formulae, when I am building applications for clients (or myself), I often use helper columns to keep the formulae simpler and more easily maintainable, as well as faster when dealing with large datasets. These extra columns are invariably hidden. Seldom (if ever) has the number of available columns been a problem even with 256, and now with 1024 in XL2007 it never will be. I practically always start with rows 1:10 and columns A:E hidden on all sheets, so F11 is my "A1". Then I always know I have spare columns and rows available for other tasks, even without inserting and hiding them within the body of data. -- Regards Roger Govier "JMay" wrote in message ... Thanks Roger I was trying to avoid the helper-column "thing"; Good to keep in mind the speed factor variances, however. Thanks, Jim "Roger Govier" wrote in message : Hi Jim If you used another helper column on your lookup table, say H7:H12 with the formula =SUM(C7:G12) then the simple =SUMIF(LookUpTable!$B$7:$B$12,Summary!A2,LookUpTab le!$H$7:$H$12) On the range sizes used, speed difference would not be noticed, but on larger arrays, it would certainly be faster than the array formula, and probably faster than the Sumproduct solution. -- Regards Roger Govier "JMay" wrote in message ... In A1:C2 I have: CC Method1 Method2 01016000 4,263.00 4,263.00 Cell B2 contains: =SUMPRODUCT((LookUpTable!$B$7:$B$12=Summary!A2)*(L ookUpTable!$C$7:$G$12)) Cell C2 contains: {=SUM((LookUpTable!$B$7:$B$12=Summary!A2)*LookUpTa ble!$C$7:$G$12)} Both Methods work fine; I'd just like to have another 1 or 2 methods of obtaining the same answer. Any suggestions? Here is my $B$7:$B$12 on LookupTable: 01016000 3,245.00 43.00 432.00 543.00 01016100 456.00 567.00 543.00 01016200 654.00 654.00 01016300 654.00 543.00 567.00 01016400 3,458.00 456.00 7,323.00 01016500 6,788.00 567.00 Thanks in Advance.. |
#8
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Roger, Thanks -- this is an added help
I use helper columns VERY Frequently also, But in this case the Table info sheet is an Essbase (Hyperion) type sheet which when it is "refreshed" It loses any pre-existing formulas, grrrrrr Much obliged for your comments (I will keep them in mind) Jim May "Roger Govier" wrote in message : Hi Jim I was trying to avoid the helper-column "thing" I hear what you say, and I respect your opinion. I do find it strange though, how averse people are to using extra or "helper" columns. Whilst I often post answers to people using perhaps long or complicated formulae, when I am building applications for clients (or myself), I often use helper columns to keep the formulae simpler and more easily maintainable, as well as faster when dealing with large datasets. These extra columns are invariably hidden. Seldom (if ever) has the number of available columns been a problem even with 256, and now with 1024 in XL2007 it never will be. I practically always start with rows 1:10 and columns A:E hidden on all sheets, so F11 is my "A1". Then I always know I have spare columns and rows available for other tasks, even without inserting and hiding them within the body of data. -- Regards Roger Govier "JMay" wrote in message ... Thanks Roger I was trying to avoid the helper-column "thing"; Good to keep in mind the speed factor variances, however. Thanks, Jim "Roger Govier" wrote in message : Hi Jim If you used another helper column on your lookup table, say H7:H12 with the formula =SUM(C7:G12) then the simple =SUMIF(LookUpTable!$B$7:$B$12,Summary!A2,LookUpTab le!$H$7:$H$12) On the range sizes used, speed difference would not be noticed, but on larger arrays, it would certainly be faster than the array formula, and probably faster than the Sumproduct solution. -- Regards Roger Govier "JMay" wrote in message ... In A1:C2 I have: CC Method1 Method2 01016000 4,263.00 4,263.00 Cell B2 contains: =SUMPRODUCT((LookUpTable!$B$7:$B$12=Summary!A2)*(L ookUpTable!$C$7:$G$12)) Cell C2 contains: {=SUM((LookUpTable!$B$7:$B$12=Summary!A2)*LookUpTa ble!$C$7:$G$12)} Both Methods work fine; I'd just like to have another 1 or 2 methods of obtaining the same answer. Any suggestions? Here is my $B$7:$B$12 on LookupTable: 01016000 3,245.00 43.00 432.00 543.00 01016100 456.00 567.00 543.00 01016200 654.00 654.00 01016300 654.00 543.00 567.00 01016400 3,458.00 456.00 7,323.00 01016500 6,788.00 567.00 Thanks in Advance.. |
#9
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
You're welcome. Thanks for the feedback!
Biff "JMay" wrote in message ... Thanks Biff; Both are Cool,, Jim "T. Valko" wrote in message : Here's another one. I might even use this one over the Sumproduct version: Defined names: Table Refers to: LookUpTable!$C$7:$G$12 ID Refers to: LookUpTable!$B$7:$B$12 =SUM(INDEX(Table,MATCH(Summary!A2,ID,0),)) Biff "T. Valko" wrote in message ... Here's one but it's not any better than what you have already. In fact, I'd never use it! But here it is: =SUM(OFFSET(LookUpTable!C$7,MATCH(Summary!A$2,Look UpTable!B$7:B$12,0)-1,,,5)) Biff "JMay" wrote in message ... In A1:C2 I have: CC Method1 Method2 01016000 4,263.00 4,263.00 Cell B2 contains: =SUMPRODUCT((LookUpTable!$B$7:$B$12=Summary!A2)*(L ookUpTable!$C$7:$G$12)) Cell C2 contains: {=SUM((LookUpTable!$B$7:$B$12=Summary!A2)*LookUpTa ble!$C$7:$G$12)} Both Methods work fine; I'd just like to have another 1 or 2 methods of obtaining the same answer. Any suggestions? Here is my $B$7:$B$12 on LookupTable: 01016000 3,245.00 43.00 432.00 543.00 01016100 456.00 567.00 543.00 01016200 654.00 654.00 01016300 654.00 543.00 567.00 01016400 3,458.00 456.00 7,323.00 01016500 6,788.00 567.00 Thanks in Advance.. |
#10
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Hi Jim
Sounds like a case for VB code to add back all the formulae, even including insertion of helper columns where necessary<bg. -- Regards Roger Govier "JMay" wrote in message ... Roger, Thanks -- this is an added help I use helper columns VERY Frequently also, But in this case the Table info sheet is an Essbase (Hyperion) type sheet which when it is "refreshed" It loses any pre-existing formulas, grrrrrr Much obliged for your comments (I will keep them in mind) Jim May "Roger Govier" wrote in message : Hi Jim I was trying to avoid the helper-column "thing" I hear what you say, and I respect your opinion. I do find it strange though, how averse people are to using extra or "helper" columns. Whilst I often post answers to people using perhaps long or complicated formulae, when I am building applications for clients (or myself), I often use helper columns to keep the formulae simpler and more easily maintainable, as well as faster when dealing with large datasets. These extra columns are invariably hidden. Seldom (if ever) has the number of available columns been a problem even with 256, and now with 1024 in XL2007 it never will be. I practically always start with rows 1:10 and columns A:E hidden on all sheets, so F11 is my "A1". Then I always know I have spare columns and rows available for other tasks, even without inserting and hiding them within the body of data. -- Regards Roger Govier "JMay" wrote in message ... Thanks Roger I was trying to avoid the helper-column "thing"; Good to keep in mind the speed factor variances, however. Thanks, Jim "Roger Govier" wrote in message : Hi Jim If you used another helper column on your lookup table, say H7:H12 with the formula =SUM(C7:G12) then the simple =SUMIF(LookUpTable!$B$7:$B$12,Summary!A2,LookUpTab le!$H$7:$H$12) On the range sizes used, speed difference would not be noticed, but on larger arrays, it would certainly be faster than the array formula, and probably faster than the Sumproduct solution. -- Regards Roger Govier "JMay" wrote in message ... In A1:C2 I have: CC Method1 Method2 01016000 4,263.00 4,263.00 Cell B2 contains: =SUMPRODUCT((LookUpTable!$B$7:$B$12=Summary!A2)*(L ookUpTable!$C$7:$G$12)) Cell C2 contains: {=SUM((LookUpTable!$B$7:$B$12=Summary!A2)*LookUpTa ble!$C$7:$G$12)} Both Methods work fine; I'd just like to have another 1 or 2 methods of obtaining the same answer. Any suggestions? Here is my $B$7:$B$12 on LookupTable: 01016000 3,245.00 43.00 432.00 543.00 01016100 456.00 567.00 543.00 01016200 654.00 654.00 01016300 654.00 543.00 567.00 01016400 3,458.00 456.00 7,323.00 01016500 6,788.00 567.00 Thanks in Advance.. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Need help implementing EPA rounding method | Excel Worksheet Functions | |||
forms that suddenly wont appear using the show method in VBA a bug or a corrupted file perhaps ? | Excel Discussion (Misc queries) | |||
onkey method | Excel Discussion (Misc queries) | |||
Method of running thru these | Excel Worksheet Functions | |||
method and function | Excel Worksheet Functions |