Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Worksheet Functions / February 2008

Tip: Looking for answers? Try searching our database.

Formula compaction

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
adimar - 03 Feb 2008 19:46 GMT
Please suggest a more compact format for this formula:
=SUMPRODUCT(
(INDIRECT("RawData!B"&$I$32&":B"&$J$32)=105)*
(INDIRECT("RawData!AA"&$I$32&":AA"&$J$32)="M6")*
(INDIRECT("RawData!AG"&$I$32&":AG"&$J$32)="CLOSED")*
(INDIRECT("RawData!P"&$I$32&":P"&$J$32)="CLOSED")*
(INDIRECT("RawData!AD"&$I$32&":AD"&$J$32)>=CalculatedData!H2)*
(INDIRECT("RawData!AD"&$I$32&":AD"&$J$32)<=CalculatedData!H3)
(INDIRECT("RawData!AF"&$I$32&":AF"&$J$32)>INDIRECT("RawData!AD"&$I$32&":AD"&$J$32))
((ISNUMBER(MATCH(INDIRECT("RawData!E"&$I$32&":E"&$J$32),Definitions!$B$3:$B$6,0)))))
)

Also, is there a way to alias frequent constructs, like
("RawData!B"&$I$32&":B"&$J$32) and replace with a parametrized syntax, like
“data(B)”, still using a worksheet formula.

Thank you.
Bernard Liengme - 03 Feb 2008 20:56 GMT
Last part of question:
I used Define Names to define: Somewhere  ---  Refers to "Sheet3!C"
In a cell on Sheet1 I use =INDIRECT(Somewhere&G1)
This returned the value from C1 in Sheet3
I could have used a shorter name but you must avoid names that could be
mistaken for cell references
best wishes
Signature

Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

> Please suggest a more compact format for this formula:
> =SUMPRODUCT(
[quoted text clipped - 14 lines]
>
> Thank you.
Herbert Seidenberg - 04 Feb 2008 02:44 GMT
Here is my suggestion, using defined names:
http://www.freefilehosting.net/download/3bddd
adimar - 11 Feb 2008 18:54 GMT
Some feedback: Having cleaned-up and re-organized my application I must tell
you the advice you gave me in this post made a huge difference in efficiency
and readability.

Please let me know if you have other “best practices” you can share.

Thank you!

> Here is my suggestion, using defined names:
> http://www.freefilehosting.net/download/3bddd
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.