Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Complex Formula in Name (Excel 97-SR2)

    OK... Jan Karel told me all about puting forumla's as defined Names... So, they are my favourite new toy! This probably means I'm over using them and am about to fall foul of some big gotchas! So, any feedback on doing this.. Also, some help with a complex formula that doesn't appear to work in this way..

    I'm testing at the moment, and they seem to work very well, no speed issues AND they gave a big impact on spreadsheet size. The model I am working on has a LOT of repeated formulas, 3,000+ per sheet. By using formulas in a defined name I have reduced my 3.4Mb model to 2.3Mb. Sort of thing I have is:

    AForecast = IF(AdjustedForecast!D$4,Actuals!D6,Plan!D6*CostAna lysis!$C6)

    =AForecast in 3,000 plus cells seems more file size efficient than the original IF's.

    OK.. THis formula works fine when in a cell:

    =INDIRECT('Bid Cost Model vWIP.xls'!Analysis&ADDRESS(ROW(),COLUMN()))*(('Bid Cost Model vWIP.xls'!IncludeDailyCost*CostAnalysis!$DF6)+('Bi d Cost Model vWIP.xls'!IncludeTandS*CostAnalysis!$DG6))*INDEX(l stInflation,CostAnalysis!D$4)

    But doesn't when I put it into a Derfined Name, I get a #VALUE! error. Is this just a little too complicated for this technique?

    As usual, any thoughts and insights always welcome.

    Peter

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Complex Formula in Name (Excel 97-SR2)

    I suspect this part:

    ADDRESS(ROW(),COLUMN()))

    What happens if you use:

    CELL("Address")

    instead?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Complex Formula in Name (Excel 97-SR2)

    That fixes the value problem.... However, CELL("address") seems unreliable on my installation of Excel 97. It returns the same value for all 105 cells in the row I am currently testing? (Just tested CELL("address") on Win2K and Excel 2K, same issue. Generally, I have always had issues of this nature with CELL().

    Thoughts?

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Complex Formula in Name (Excel 97-SR2)

    Peter,
    From recollection, unless you pass CELL an address, it returns the info about the last changed cell. That may be the cause of your problem.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Complex Formula in Name (Excel 97-SR2)

    For complex formulas, consider putting them into VBA functions (so called UDF or user defined functions). If they are unique to your worbook keep them there, if used generally, put them into an add-in. The main advantage is that you can develop/test/document them much better.

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Complex Formula in Name (Excel 97-SR2)

    Indeed, cell has two arguments:

    CELL("type",Address)
    If address is omitted, it returns the address of the active cell (sorry, I assumed the address of the caller). So use:

    =CELL("Address",INDIRECT("rc",False))
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Complex Formula in Name (Excel 97-SR2)

    Rory

    That's it!.. This works:

    CostAnal = INDIRECT(Analysis&CELL("address",CostAnalysis!D6)) *((IncludeDailyCost*CostAnalysis!$DF6)+(IncludeTan dS*CostAnalysis!$DG6))*INDEX(lstInflation,CostAnal ysis!D$4)

    '=CostAnal' in the 3,000+ cells in the CostAnalysis worksheet, rather than the formula above, reduces overall size by 250K.

    Thanks Rory & Jan Karel... I'll be back!

  8. #8
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Complex Formula in Name (Excel 97-SR2)

    I'm not adverse to VBA, I was writing stuff in in VB and Wordbasic 10 years ago, or is it even longer ago? That said, given the likely distribution of some of the models I am working on and the on-going issues around macro viruses, it's 'simpler' to avoid VBA.

    The next project I want to work on, bearing in mind this is not my day job, is a contract diary/calendar. I will want to emulate the Outlook recurring meeting/appointment features, can't see anyway of doing that without VBA. (Not that it won't stop me trying!)

    Regards
    Peter

  9. #9
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Complex Formula in Name (Excel 97-SR2)

    Update...

    Using the Formula in a Name technique, I have reduced the size of my spreadsheet model from 3.36Mb to 1.81Mb. i.e A reduction of 1.55Mb. Steve's suggestions on reducing complexity of IF's removed about 2.6Mb from the original which was nudging 6Mb. i.e. The two techniques combined have reduced the size by over two thirds. This now compresses down to a 400K zip file which is not too bad for e-mailing around.

    Thanks to all for your assistance.

    Peter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •