Logic, computing and people

Excel has sophisticated methods available for users but it is easy to misunderstand what the program designer really meant. The Intersect method could be one of these. I first used the method a little over 20 years ago. It allowed me to post a value in named column, in respect of specific transaction (formed by a row). At that time, the real advantage of this approach the location of the column could change without my having to rewrite the code. It would continue to be posted to the correct cell for the purposes of the overall program. My current task – one range within another - sounds very similar. Indeed, when searching the web, the Intersect method was offered as a potential solution . However, there are some crucial / fundamental differences. I was not looking for overlapping ranges. Rather, the whole of the smaller range had to be contained within the boundaries of the larger one. It became clear that I had examine each of the boundaries of the larger range individ...

I have programmed in MS Excel using VBA (Visual Basic for Applications) fairly regularly over the past 25+ years. This makes me experienced but not necessarily expert. Frustration over the past several days seems to have demonstrated the lack of expertise, particularly in handling hyperlinks between Excel workbooks. The project involved building a hyperlink bridge between specific pages/worksheets within two workbooks. The environment wasn’t too difficult since both workbooks would be housed in the same directory. However, the two workbooks would have a parent / child relationship. The code was designed to build a new control worksheet in the parent workbook and hyperlink it to a specific worksheet and cell range in a child workbook book. The child workbook would be generated entirely in code before being saved. This didn’t sound too difficult and it wasn’t; until it came to trying to create the hyperlinks between pages of the different workbooks. The first thing to n...

Excel is well known as an environment in which you can create models. Many modellers secure the integrity of their model by a combination of protecting and/or hiding sensitive cells. This is understandable when the model becomes the live or final version. Nevertheless, I have railed against using Excel in this way for 3 decades but have failed to convince colleagues. Using Excel to model solution methods in Sudoku is intended to demonstrate why it is such an excellent environment for exposing the strengths (and weaknesses) of any particular solution method. I concentrate here on two very similar methods referred to as “Intersection”. Background I have published a number of blogs / articles covering the more straightforward elements of users interacting with their Sudoku puzzles. This article builds on the previous publications. Title Source Date Spatial Modelling Techniques in Microsoft Excel EuSpRIG 14th Annual Conference, Greenwich July 20...

The foods we eat vary hugely, even within a relatively small country like the United Kingdom. There is no question of providing the calorific value of every foodstuff that forms your every day diet. The table below was created to meet the needs of my wife and I, while we were on an 8-week diet.  While the specific items in our dietary list may be eccentric, I hope links to our sources of information about calorie content will lead you to the data you need for your own food list.  Much of the data is sourced from the United States Department of Agriculture (USDA)  https://ndb.nal.usda.gov/ndb/  . I have not searched the USDA database, rather Google searches for food calorie information most often provides as the most important source. I use this where appropriate. Ingrediemt Calories Unit Multiple Source Web Page apple 52 g 100 www https://www.healthline.com/nutrition/foods/apples ...