1. 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. 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.

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]

3. 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.

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.

5. Will do - Thanks

6. [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.

7. 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. [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.

9. 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
•