Results 1 to 7 of 7
  1. #1
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Making long Formulas more readable (All)

    When entering long formulas, use Alt + Enter to force the text to wrap for greater readability. (See Picture)

    I can't remember where I read this to give credit - but it was a real "of course you can do that moment". <img src=/S/doh.gif border=0 alt=doh width=15 height=15>
    Attached Images Attached Images
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  2. #2
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Making long Formulas more readable (All)

    Aha <img src=/S/exclamation.gif border=0 alt=exclamation width=15 height=15>
    Regards
    Don

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Making long Formulas more readable (All)

    Thx for the tip... Catherine. This is a handy one! This will even be useful to do in Excel 2007...even though the formula br can be opened up, it still makes the formula more structured.
    Regards,
    Rudi

  4. #4
    Silver Lounger
    Join Date
    Aug 2001
    Location
    Canton, Ohio, USA
    Posts
    1,716
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Making long Formulas more readable (All)

    Thanks this will be useful.
    H Lewton

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Sydney, Australia, New South Wales, Australia
    Posts
    251
    Thanks
    0
    Thanked 4 Times in 4 Posts

    Re: Making long Formulas more readable (All)

    While this is a useful technique, I am compelled to issue a warning against long formulas.
    Long formulas are always difficult to read and understand.
    In this example, it would be far better to create a UDF:

    <pre>=GetRoomRate(B11)
    </pre>


    Depending on the number of times this formula has been copied, using a UDF may also make your spreadsheet smaller.

    Then in the VBA editor, the function would look like this:
    <pre>'Function to calculate the room rate for a specific date
    Function GetRoomRate(dDate as Variant) as Variant
    'First check that it was actually a date passed to us
    If ...
    ....
    End If

    'Now get the actual room rate forr that date
    Select Case dDate
    ....
    End Select

    End Function
    </pre>


    This creates a much more understandable and maintainable result.

    Peter

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Making long Formulas more readable (All)

    I agree that one should avoid very long formulas, if possible.
    However, using VBA is not always an option - users can't cope with the macro warnings, or company policy forbids the use of macros, for example.
    I prefer using cells (usually in columns which may be hidden) with intermediary results. This makes it easier to test and maintain the formulas.
    But if you're forced to use a long formula, for whatever reason, WebGenii's tip helps making it more readable.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Making long Formulas more readable (All)

    In addition to all of Hans' other excellent points another reason to use a formula (or even intermediate formulas) instead of a UDF is that the formulas are much more efficient and calculate faster than UDFs.

    Steve

Posting Permissions

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