Replies

Rich – I think Hans’ use of the Match formula is cleaner and will be easier to maintain, and probably execute faster, too!

Rich – will you always have three Class_Dates listed, or could there be more – for instance, if every weekly class was listed?

With only three you could generate the “Yes” and “No” responses by formula (warning, aircode) – something like:

=IF((MONTH($E6) = A$4) + (MONTH($F6) = A$4) + (MONTH($G6) = A$4) > 0,"Yes","No")

where row 4 contains the month values – “5” in the column coresponding to May, “6” for June, etc, and the columns E through G contain the Class_Dates as shown. You would copy this formula to columns A-D and the result will match the sample you provided. You can then add the “Yes’s” and “No’s” with a “Countif” or an array function.

If there are more than three or four columns (like twenty or thirty) then this sort of code becomes very difficult to maintain, and I would suggest looking at a database solution, or at least at normalizing your data so that each record represented one class time. That might leave you with multiple records per student, which requires two steps in determining the number of students who attended any class in each month. You would add the number of classes in -say- August for each student, and then count the number of students where the class count >= 1. That means a little more work on processing, but it will make maintenance much easier.

Thanks, Hans. Not the answer I wanted – but it was the answer I expected

If you prefer not to have the data source open, can you include a “before close” routine that will rename the required named range to the same range that would have been calculated for the dynamic range? That way the dashboard file should be able to read the data for the Pivot Table even if the data file is closed.

Further to Hans’ suggestion – write (or record) a macro in your Personal.xls:

Sub CenterAcrossColumns
With Selection
   .HorizontalAlignment = xlCenterAcrossSelection
   .MergeCells = False
End With
End Sub

and then either delete the existing “Merge and Centre” button and assign this macro to a new button, or just assign the macro to the exisiting “Merge & Centre” button. It will save this kind of aggravation in the future, since you will not have to deal with merged cells.

[indent]

---

…but also cheered up from knowing that the world does contain folk who go out of their way to help others, all for free.

---

[/indent]
Well – there’s the Glory, of course…

It is difficult to come up with the top ten strengths or features of Excel – it’s like asking for the top ten features of an incredibly well-equipped tool shop: it all depends on what you need to do. In general Excel is exceptionally strong wherever you need to work with numbers – storing, manipulating, or presenting numeric data or results: obviously that includes financial applications, but it extends to engineering, systems analysis, science labs, statistics, and on and on – one developer has used Excel to run engineering design and even produce drawings by running Visio through VBA. Excel is pretty strong whenever you have lists – it is a rough and ready database; not the best, but certainly the most widespread.

I think that the strongest “feature” of Excel is its sheer ubiquity, driven by its flexibility: while it is the best of breed as a spreadsheet, it is only a reasonable tool for many other purposes – but it is there, it will work “well enough,” and the user is reasonably familiar with it (and knows that his audience will have a copy) – so it becomes the tool of choice. Eventually a little ecosystem of specialized users and developers huddles around Excel, and they create the add-in and attachments and refine the approaches that make it a better tool for that particular operation than it was before. For an example, look at the number of Monte-Carlo or statistical add-ins for Excel, or genetic solver add-ins, engineering add-ins, and so on. All that said, MY top-ten list would look something like this:

1. user friendliness – Excel is approachable – anyone can start it up, and have at it. Now, that leads to some really stupid uses: I once had a client who used Excel as an expensive, specialized word-processor, since it did numeric alignment so nicely. This client entered invoice line items into an Excel spreadsheet, put the item cost in the next column, then calculated the total and entered it at the bottom of the invoice… she was quite surprised to learn that the computer could do that for her – without mistakes!
2. reasonable WISIWYG – it might be nice if Excel was exactly WYSIWYG – but I am not sure if the extra processing cycles would be worth it. As it is, I have a pretty good idea what my output will look like as I work, and a very good idea if I use print preview – it is pretty rare that the output is so different than what I see on screen that it requires more than some minor tweeks to get it right, and the output is pretty good quality.
3. financial functions – I do economic evaluation of large infrastructure projects, typically (although not exclusively) in the energy sector (pipelines, powerplants, gas processing plants and the like). All of these evaluations rely on the time-value-of-money calculations in Excel. Because my background is in finance, virtually every spreadsheet I have ever dealt with for any length of time has used these functions – and while Excel is used for many other things, I suspect financial applications are still the largest singe user segment
4. pivot tables – these allow users to extract data from relatively large data sets easily, intuitively, and quickly. They are extraordinarily powerful, but dramatically under-used. I have one file that lists hourly transaction prices for spot energy sales for a four year period (roughly 44,000 data points, with three elements per point). By being careful with the data format (which requires some massaging from the format the ISO provides it in) I am able to use the data as input to a pivot table, and I can then summarize and extract data – typically daily pricing profiles by hour – fairly quickly (it takes some re-calc time on my slow old computer), but with very little effort; I just click the buttons and wait a couple of seconds!
5. array formulas – like pivot tables, array formulas are incredibly powerful – but they seem more difficult to understand, or at least are less intuitive. Once a user begins to use them, though, they can provide a compact way of extracting information that either just isn’t available, or would take many steps to obtain
6. extensibility – it’s nice to be able to write a UDF when required, or to automate some tasks, but I am thinking more of the value of third-party developers having access to Excel’s object model. There is no way that I could write a VBA routine do do the sort of things that Palisade does with @Risk – not only is it beyond my abilities, I could never justify the time and expense for my use of the functionality even if I could do it for myself. Since Excel allows such easy access to the object model and can be automated with VBA (and calls to dlls written in VB or C, etc) there are any number of add-in and add-ons that can make Excel do things well beyond its “out of the box” capabilities.
7. pretty good display – here I am thinking of the charting abilities – sure, it is possible to use dedicated graphics packages that will produce truly stunning output – and if I was publishing the annual report for IBM or DuPont, or an illustrator for Time Magazine, that’s what I would use. But this sort of output quality comes with additional cost and complexity; as it stands, Excel produces pretty good output, pretty flexibly, in a wide variety of chart styles and formats. With a little care, the output is perfectly suitable for inclusion in documents (or the dread powerpoint presentation) or to be handed out “as is” for seminars, workshops, or presentations, even at pretty high levels
8. auto filter / advanced filter – I noted that Excel is a “rough-and-ready” database, and it is features like “auto filter” and “advanced filter” that make it so. It takes a huge leap of faith to make Excel act like a relational database, and a significant investment of time and effort – so significant that it is easier, faster, and more secure to go out and buy a copy of Access (and even cheaper to use the Open Office equivalent). But for managing flat files in a known environment with a familiar interface, Excel is hard to beat. I wouldn’t want to bet my life on it, or even significant amounts of money – but I am perfectly happy to bet the re-certification dates of minor hockey officials.
9. breadth of product offering – several times I have noted that Excel is “pretty good” at a particular task: the variety of things that can be done and the flexibility of the format are just astounding. Excel is like a Swiss Army knife – it may not be the best tool in the entire toolbox for a particular task, but it is the best tool to replace the entire toolbox for an unknown task. Okay – so I only got to nine; so sue me!

Okay – here is the third installment of the Tornado Chart Maker utility. In this iteration I have:

• Completed the implementation of Hans’ suggestion not to use “Tools” to identify the menu I am adding an item to, but use the ToolBar ID instead, so as not to screw up in foreign language editions. My first time ’round I forgot to make the change to the toolbar removal logic (smooth, eh?)
• Added error handling if the user is about to overwrite existing data – the routine informs him/her of the problem, tells him to try again, then exits
• Added some error handling for unsuitable selections of starting range or the median value range
  [/list]Comments or suggestions are welcome. The zip file also includes a sample data file – more extensive than would usually be found in the wild.

I would enter an array function:

=sum(if(A$1:A$35 “”,1) formula has to be entered with ctrl-shift-enter

since I could change the test to be = “Doug” or >5 etc. at some later date

On the other hand, if I know it is just non-blanks I would ever be worried about,

=counta(A$1:A$35)

will work, too…

If you copy or move NewA and NewB from workbook B to workbook A you can then change the “links” in A (which will reference wb to reference wb A.

• Save both wb’s – particularly, save wbA with a new name – just in case it doesn’t work…
• Copy the sheets NewA and NewB from wbB to wbA the version with the new name
• Close wbB
• Save wbA, then open the menu item “Edit | Links this will open a dialogue box with all the links listed
• Click on “Change Source” on the right (if there is more than one source listed, make sure you have selected the reference to wbB)
• This opens a regular file list dialogue box – click on the name of wbA and Okay, then clear the ‘edit links’ dialogue box (the link to wbB will have disappeared)
  [/list]

[indent]

---

… now works well on my Dutch language system.

---

[/indent] Glad to hear it!

[indent]

---

…you don’t check whether the selected range/current range is suitable…

---

[/indent] But error handling is no fun… Actually, Hans, that is a very good suggestion; thank you!

I have been thinking that I should add error handling for the situation where the user is going to overwrite existing data – first; it will aggravate them, especially since the VBA will clear the undo stack – (see Jan Karel’s recent posts at Daily Dose of Excel – but I am not going to go to that length), and second; it may screw up the creation of the chart. I should add the error condition of a poor selection of starting point, as well.

At the moment the logic assumes that if a single cell is selected then the user intends to chart all of the ‘current range’ – I think that is a pretty reasonable assumption, although it is not infallible. If there is more than one cell selected then only the selected range will be charted – the idea is to match the way Excel creates charts, but the matching isn’t exact since the user selects the data points, not the comments. I am concerned that a confirmation dialogue will be aggravating, and had considered a confirmation with a “don’t show this warning again” option – I suppose the response is usually recorded in the registry, but since I am already storing information in the Add-In worksheet, I could save it there.

Any thoughts or comments would be welcome.

I have fixed the two problems Hans noted, and I have also added the facility to adjust the shading on the colour bars. I will be very interested to hear your comments.

You were actually calculating an amortization on a 59 month lease by treating the buyout as due at the start of month 60: in fact, you have already paid for the use of the asset for that month, and will only have to buy it at the end of the month. It made a very small difference on the “$1” buyout lease, because you were only talking about accelerating a payment of $1 – five years out, the difference is lost in the rounding. On the $32K payout, though, the difference was enough to affect your interest calculation.

See the attached file for the revised example.

[indent]

---

I like the descriptive titles, my version doesn’ t have that.

---

[/indent]

I borrowed a bit of code (or at least, a lot of understanding of how chart labels work) from Rob Bovey’s XY Chart Labeller for that! Note that the labels remain live – changes will be updated on the chart – and can be formatted with line breaks so they fit better on the chart.

Please try it out and let me know what you think could be improved – feel free to have the other team that uses your version whack away at it as well.

Thanks Hans – I will make those two changes.

Dean

[indent]

---

I have a large financial model that is generic (which is turning out to be much harder to do than a specific one)

---

[/indent]

They always are. The people with the least experience in modeling or analysis are always the ones to say “I think we should have a ‘standard’ model for all our projects…” – to which the only correct response is something along the lines of “Sure; just show me the standard project and I’ll get right on it.”