Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Loungers,

    I have developed the following formula - that seems to work well.

    ="Service "&IF(MAX(B3:B24)-MAX(D324)=-1,"not due until "&MAX(D324)&" Hours "&MAX(B3:B24)-MAX(D324)&" hour to go",IF(MAX(B3:B24)<MAX(D324),"not due until "&MAX(D324)&" Hours"&MAX(B3:B24)-MAX(D324)&" hours to go",IF(MAX(B3:B24)-MAX(D324)=0,MAX(B3:B24)-MAX(D324)&" Hours Overdue",IF(MAX(B3:B24)-MAX(D324)=1,MAX(B3:B24)-MAX(D324)&" Hour Overdue",MAX(B3:B24)-MAX(D324)&" Hours Overdue"))))

    As a matter of interest and some learning opportunities, more than anything else, has anyone got any ideas on how to simplify this and achieve the same results

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post
    At first glance, you might assign names to the ranges: B3:B24 and D324. Depending on the names that you assign, it may not make the formula any simpler or shorter, but it could help avoid mistakes in writing the formula.

    Additionally, you could have used multiple cells to achieve the desired result, rather than using a monster formula in one cell... But, that just provides an alternate method, not necessarily a simplified method. As an example, your formula makes several references to the maximum value (MAX) in the ranges B3:B24 and D324. You could have placed the two MAX formulas in other cells, named the two cells, and then use a final formula that refers to the named ranges.

    Say Cell A10 contains the formula =MAX(b3:b24) and Cell A11 contains the formula =MAX(d3:d24). A10 and A11 are named Form1 and Form2, respectively. Then your entire formula could be rewritten as such:

    ="Service "&IF(FORM1-FORM2=-1,"not due until "&FORM2&" Hours "&FORM1-FORM2&" hour to go",IF(FORM1<FORM2,"not due until "&FORM2&" Hours"&FORM1-FORM2&" hours to go",IF(FORM1-FORM2=0,FORM1-FORM2&" Hours Overdue",IF(FORM1-FORM2=1,FORM1-FORM2&" Hour Overdue",FORM1-FORM2&" Hours Overdue"))))

    This is 120 fewer characters than your original formula. But it uses two additional cells on your worksheet. And still, I'm not sure it's any simpler; it's just an alternate route to the same destination.





    [quote name='verada' post='763518' date='04-Mar-2009 21:34']Loungers,

    I have developed the following formula - that seems to work well.

    ="Service "&IF(MAX(B3:B24)-MAX(D324)=-1,"not due until "&MAX(D324)&" Hours "&MAX(B3:B24)-MAX(D324)&" hour to go",IF(MAX(B3:B24)<MAX(D324),"not due until "&MAX(D324)&" Hours"&MAX(B3:B24)-MAX(D324)&" hours to go",IF(MAX(B3:B24)-MAX(D324)=0,MAX(B3:B24)-MAX(D324)&" Hours Overdue",IF(MAX(B3:B24)-MAX(D324)=1,MAX(B3:B24)-MAX(D324)&" Hour Overdue",MAX(B3:B24)-MAX(D324)&" Hours Overdue"))))

    As a matter of interest and some learning opportunities, more than anything else, has anyone got any ideas on how to simplify this and achieve the same results[/quote]
    - Ricky

  3. #3
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Tricky - thanks for the reply.

    Will think about assigning range names, I guess in terms of the actual calculation methodology there are no real alternatives to shorten the whole thing.

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='verada' post='763528' date='05-Mar-2009 00:15']Tricky - thanks for the reply.

    Will think about assigning range names, I guess in terms of the actual calculation methodology there are no real alternatives to shorten the whole thing.[/quote]

    Not that I can see. CONCATENATE formulas can be rather long, depending on the desired output [or result]. The result of your formula would contain a fair amount of text. The text is what makes it long. The functions within are primarily MAX functions. Replacing the MAX functions with a 3 or 4 letter rangename seems to be the quickest way to reduce the monster formula. I think I was able to reduce it by 120 characters.

    If it works the way you have it, I'd leave it alone. Looks good to me!

    Your formula also contains a series of nested IF statements. One of the other Loungers may have some advice for simplifying those - be sure to check back later; there could be some additional replies to your posting.
    - Ricky

  5. #5
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Will do - Thanks

  6. #6
    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
    [quote name='verada' post='763518' date='05-Mar-2009 03:34']Loungers,

    I have developed the following formula - that seems to work well.

    ="Service "&IF(MAX(B3:B24)-MAX(D324)=-1,"not due until "&MAX(D324)&" Hours "&MAX(B3:B24)-MAX(D324)&" hour to go",IF(MAX(B3:B24)<MAX(D324),"not due until "&MAX(D324)&" Hours"&MAX(B3:B24)-MAX(D324)&" hours to go",IF(MAX(B3:B24)-MAX(D324)=0,MAX(B3:B24)-MAX(D324)&" Hours Overdue",IF(MAX(B3:B24)-MAX(D324)=1,MAX(B3:B24)-MAX(D324)&" Hour Overdue",MAX(B3:B24)-MAX(D324)&" Hours Overdue"))))

    As a matter of interest and some learning opportunities, more than anything else, has anyone got any ideas on how to simplify this and achieve the same results[/quote]

    It seems to me you could shorten it straight away to:
    ="Service "&IF(MAX(B3:B24)-MAX(D324)=-1,"not due until "&MAX(D324)&" Hours -1 hour to go",IF(MAX(B3:B24)<MAX(D324),"not due until "&MAX(D324)&" Hours"&MAX(B3:B24)-MAX(D324)&" hours to go",IF(MAX(B3:B24)-MAX(D324)=0,"0 Hours Overdue",IF(MAX(B3:B24)-MAX(D324)=1,"1 Hour Overdue",MAX(B3:B24)-MAX(D324)&" Hours Overdue"))))

    but I would also put the MAX(B3:B24)-MAX(D324) calculation into a separate cell.
    Regards,
    Rory

    Microsoft MVP - Excel

  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
    In addition to the suggestion of putting MAX(B3:B24)-MAX(D324) into a cell and using that as a reference (this will not only make the formula smaller, but save on calculation overhead, the section of checking for "= 0":

    IF(MAX(B3:B24)-MAX(D324)=0,"0 Hours Overdue"

    Is redundant as this will be the same as the other part. You only need to separate out the "=1" so you use Hour instead of Hours. Whether it is =0 or equal to any number rather than 1, you get the "same result": the difference in Hours overdue.

    Steve

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='verada' post='763518' date='04-Mar-2009 23:34']As a matter of interest and some learning opportunities,[/quote]
    Speaking of learning opportunities ...
    The attached workbook contains two similar functions.
    The first is, as near as I can get it on one cup of coffee, a literal translation of your beautiful excel expression.
    The second is a slightly-tidied up version of the first.
    Neither function has been properly tested.

    If you're new to VBA, open the workbook and use the function picker (the little fx thingy) and select the category "User defined Functions".
    Scroll down to either function, select the range of cells as arguments, and enjoy.

    I often prefer a udf (VBA) representation because I can then
    (1) use the function everywhere
    (2) use the fucntion with different ranges. e.g. I am not confined to strictly rows 3 through 34.
    Attached Files Attached Files

  9. #9
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Thanks to all for the replies and suggestions.

    Will be able to use the ideas in other application also.

Posting Permissions

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