Bids, tenders, requests for information and word counts…

I won’t go into the details (internal company stuff that shouldn’t be on a blog) but at the moment I’m working on a lot of bids, tenders, requests for quotations, requests for information, etc., etc.

I’ve done this sort of work before and it’s not a great fit for me but sometimes it has to be done. It’s my turn. But I hadn’t realised until recently why it is that I struggle so much…

Over the years, I’ve learned to deal with ambiguity; I’ve learned how to respond without having all the facts. I can write convincing copy (at least I think I can) and I can usually spell (despite a colleague suggesting yesterday that I should check a dictionary because “siloes” didn’t look right to him and maybe it should be “silo’s” – arghhh!).

It was my wife who pointed out to me that the very same attributes and skills that help me as an architect (general pedantry; taking the time to consider the various consequences of choices made; a desire to put in place controls to get things right and to do them well) hinder me in a high-pressure sales environment where I don’t have time to think and where everything is urgent/important and needs to be done NOW (or very soon after now)…

…and relax. Because it’s Friday night. And, in a short while, I will have a beer, or a glass of wine, in my hand.

Anyway, what is the point of this drivel? The ranting ramblings of an Architect? No. Ah, yes, word counts.

Counting words in a document – or in a cell in a spreadsheet…

Lots of bid responses are limited in the number of words that can be accepted. Often, the tool I’m using is Microsoft Word and it’s pretty easy to show the word count for a document or part of a document. Sometimes though, I’m using a different tool to create a document. Like Microsoft Excel.

I was working on a form of response that lists several skills and requires a response of less than a hundred words for each. Sounds easy? Maybe, but thirty 100-word responses are still 3000 words… and only having 100 words to detail experience can be limiting sometimes.

I needed a method to count the number of words in a cell of the spreadsheet and, as usual, I found the answer online:

=IF(LEN(TRIM(A1))=0,0,LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1)

Basically, this compares the length of a string with the length of the same string with all the spaces removed and adds 1 (for a single word with no spaces) or returns 0 if there is nothing in the cell (the TRIM function removes any extra spacing). It’s pretty crude but assuming no hyphenated words or solidi (oblique slashes) it will give a good enough count of the number of words in the cell. Definitely a time-saver for me…

Bonus tip

Excel does have a spell checker – it’s just not very obvious. Just press F7 (or go to the Review menu, then choose Spelling). This only works in the desktop client – not Excel Online.

Short takes: Excel tips to display the worksheet name and validate data; editing Microsoft Project files stored on SharePoint; and an XPS to PDF conversion service

Another collection of mini-posts based on recent IT trials and tribulations…

Excel tips to display the worksheet name in a cell and to validate data

Last week, I was working on an Excel spreadsheet that acts as a plan for a series of tests. Each sheet has the same format, with some conditional formatting and associated logic to total up passes/fails and give a RAG score for the sheet. Those RAG scores are presented in an overview page – and data is copied between cells so that information is only populated once but appears on every sheet.  I’m quite pleased with the result but I did need to work a little on some of the tricks.

Firstly, data validation in lists (for the pass/fail). This is fairly straightforward but I usually forget how to do it so it’s worth reading the TechNet Productivity Hub post on restricting data entry in Excel with lists.

The second trick was to read the name of each worksheet and use that information in a cell (so I could name a worksheet after a set of tests, and see that name displayed as a header on the page too). Here, the SuperUser site came to the rescue and the code I needed in the cell was:

=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1),1))

Incidentally, I also needed to look something up that I’ve blogged about previously: if a cell shows the formula rather than the result, check the formatting is General and not Text.

Editing Microsoft Project files stored on SharePoint

Much as I try, it seems I can’t avoid working with Microsoft Project. Unfortunately, when working directly from SharePoint the files are opened read-only. The answer, it seems, is to work on a synchronised local copy – as described by Victor Butuza on his Microsoft Office SharePoint blog.

XPS to PDF conversion web service

Every now and again, I find myself wanting to create a PDF from an email, just to upload a receipt to Xero (the expenses system I use at work). Unfortunately Xero isn’t happy with XPS files – and Windows 8.1/Outlook don’t create PDFs, but a quick Internet search turned up XPS2PDF, a simple, fast and apparently secure way to convert my files.  There’s an API for those who want to make the conversion programmatically too.

Excel formula for calculating a price, based on a known cost and margin percentage

A couple of years ago, I blogged about the difference between margin and markup. Since then, there have been a number of occasions when I’ve wanted to know the formula to take two cells in Excel, one with a cost and the other with a margin percentage, and then calculate the price. I’m sure I’ve blogged that too, but I can’t find it now – so here it is (after I worked it out again this afternoon)…

Using the example above, the formula to calculate the price in cell C2, based on cost in A2 and margin in B2 is =A2/(1-B2).

Fighting with formulae in Excel

Excel. Love it, or hate it, there are few of us in white collar jobs who don’t have to grapple with it from time to time and, earlier this evening, after OneNote had yet again decided that syncing shared notebooks via our ageing SharePoint platform as not going to work, I needed to come up with a new way of storing actions and decisions for the management team I’m one member of.

I would have liked to create a SharePoint task list but I don’t have the necessary permissions, or the inclination to find out who can give me them (and then to justify why I need to do this). Which led me back to the tool upon which so many business processes are built: Excel.

On the way, I grappled with some formulae, so I decided to knock up some notes for the next time this happens – or the next person who needs to do something similar…

Nested IFs, SEARCHes and ISERRORs

My Excel workbook is simple: two tabs (“Decisions” and “Actions”).  The Decisions tab looks after itself: three columns with a date, a decision, and the rationale behind it.  Actions is a little more complex (read over-engineered).  I have date, status, action, owner, target date, and notes. Then I want the status to be auto-populated based on the notes (If the notes say complete, then status is “Completed”; if the notes include the word cancelled then the status is “Cancelled”, otherwise the status is “Open”).

This was the first challenge.  Excel doesn’t have an ELSE in its IF function, but you can nest IFs as follows:

IF(logical_test, [value_if_true], (IF(logical_test, [value_if_true], [value_if_false]))

(or use one of the LOOKUP functions).  I combined this with the SEARCH function to look for instances of the word “Cancelled” in the notes and everything was working well with results returned for a match on “Complete”, or an instance of “Cancelled” but then the catch all value if false always returned a #VALUE! error.

As SEARCH will always return #VALUE! if the search string is not present, I started to look for workarounds.  Some googling told me that I need to test for an error condition with the ISERROR function but that didn’t seem to solve the issue. So I reversed the logic – check for the error first andthen take action accordingly:

=IF(F2="Complete", "Completed", IF(ISERROR(SEARCH("Cancelled",F2))=TRUE,"Open", IF(SEARCH("Cancelled",F2), "Cancelled")))

It may be easier to understand this in some form of pseudocode:

IF cellvalue="Complete"
{
status = "Completed"
}
ELSE
{
IF cellvalue doesn't contain "Cancelled"
{
status = "Open"
}
ELSE
{
IF cellvalue contains "Cancelled"
{
status = "Cancelled"
}
}
}

Conditional formatting based on cell contents

The next thing I wanted to do was to format each item in the action list according to it’s status and/or date. I wanted to colour completed actions grey; mark cancelled actions as grey and strikethrough the font, then add a red/amber/green status on open actions according to the target date. Conditional formatting did the trick here but I wasn’t sure how to highlight a row based on the contents of one cell in the row.

I found the answer on the HowToGeek site and key to this is not worrying that the reference to the cell used to test the logic is a single cell – Excel is smart enough to work out that you need the same column on the next row when evaluating the condition there.

So, I ended up with six rules, three of which use the TODAY function to evaluate whether the task is overdue or not (or due today):

Formula Format
=$B2="Cancelled" AaBbCcYyZz
=$B2="Completed" AaBbCcYyZz
=$E2="" No format set
=$E2<TODAY() AaBbCcYyZz
=$E2=TODAY() AaBbCcYyZz
=$E2>TODAY() AaBbCcYyZz

It may all be a bit OTT, but I for one work in a very visual manner – I need documents to show me something, not just rely on reading the text.  Hopefully this will work out to be a useful tool for us…

Short takes: Android screenshots; LinkedIn invitation preferences; and hiding Excel headings

Every now and again, in an attempt to close down some “must blog about that” tabs in my browser, I write one of these “short takes” posts… here’s the latest snippets from the world of Mark…

Screen-grabbing on Android

There have been a couple of occasions recently when I’ve wanted to take a screenshot on my Samsung Galaxy S3 Mini… but couldn’t find how to…

Having to Google to work out how to take a screen shot in Android is… a little odd… but there are a couple of methods mentioned in an Android News post (and six more methods here).  And the reason for the most recent screengrab?  This:

Why does LinkedIn sometimes need an email address before you can send an invitation to connect

I’ve noticed a few times recently that I’ve needed to supply someone’s email address (to prove I know them) when connecting on LinkedIn.  Whilst some Internet reports suggest this is because you’ve been marked as having too many “I don’t know this person” reports, it seemed to be inconsistent for me so that’s not the only reason this happens.  It turns out that there’s an option deep within LinkedIn’s preferences to only receive invitations from members who know your email address.  Now that I know about it, I’ve activated it on my profile too…

Displaying/hiding row and column headings in Excel

I received an Excel workbook recently where all of the row and column headings were missing.  Confused? I certainly was (especially as I wanted to add some columns) but it seems it’s a simple configuration option (just maybe not that commonly used?!), at least in Excel 2013 (your mileage may vary with other versions).

Business intelligence required…

Up and down the country, businesses are running on Excel, instead of using a proper business intelligence (or even management information) system. The one I look after is no different but, as I pieced together yet another spreadsheet last weekend, I learned a few Excel tips that might be useful to share…

=SUMIF()

I’ve been trying to pull together a resource forecast in order to work out how quickly to grow my team. The approach I look was to list all the projects we have coming through, with headcount requirements split out by grade, then to total each column based on the grade of staff required.

Seems fair enough, but the trick to making this work is reading a cell and then only including its value in the total if a condition is met (e.g. the indicated grade matches the one I’m adding up).

Stack Overflow came to my rescue, describing Excel’s SUMIF() function

In my case, the formula was something like:

=SUMIF(E4:E148,E154,F4:F148)

Where E4:E148 contained the grades of people for each identified project, E154 contained the grade I was looking for (e.g. Exchange Designer) and F4:F148 were the numbers of people needed for each project that month. Repeat for each grade, and then for each month, and a table of resource requirements can be built up…

There may be better ways to do this, but it will save me some time adding up the totals each time I revisit the task list…

More margins…

Of course, knowing how many people I need is one thing – making some crude assumptions about the likely revenue they might attract to see if I’m close to my numbers for the year is the next question I’ll be asked.

Last week, I blogged about the difference between mark-up and margin, and this week I needed to put that into practice.  I found a forum post that explained the formula (sale price = 100/1-margin * original cost), so I put that into practice, multiplying by a day rate, an assumed number of working days in the month and the total of that grade of person:

=(D165*(1/(1-D174)))*D175*F154

Which translates to:

=(dayrate*(1/(1-margin)))*number of days*number of people

Displaying data in 1000s

The last part was displaying data. Some of the revenue numbers I ended up with are big – and I’m only interested in 1000s of pounds, so I needed to adjust the formatting of the results.  The trick here is to use a custom number format on the cell of 0, (zero comma) for thousands (or 0,, can be used for millions). Add a K or an M on the end for units, and a currency symbol up front too. You can also add a decimal point using 0.0, (e.g. £0.0,K for £1500 to be displayed as £1.5K) or, if the numbers get into the millions, then try something like £0,000,K.

Short takes: searching in Outlook; duplexing in Excel; merging in Word; and going wild in Salesforce

This week I’ve mostly been… working in pre-sales. Consequently, this is perhaps not the most exciting blog post I’ve written… but hey, it’s a post and there haven’t been many of them recently!

First up: searching Outlook

Since I changed jobs in April, my email volume has increased by 300x. My mail archive has more messages in it as we approach the end of June than it did for the whole of 2012, and most of them have been sent/received in the last three months.  In short, being able to quickly and accurately search Outlook is important to me.

Microsoft’s website has some good advice for narrowing search criteria for better results in Outlook – for example, if you’re looking for that email from Mark Wilson with the attachment you needed? Try from:"Mark Wilson" hasattachment:yes.

Next: opening two Excel workbooks side by side

If someone sends you a spreadsheet that you need to complete, and there’s information to pull from another spreadsheet, it can be a nuisance to keep switching back and forth between windows inside the application. The answer is to use Task Manager (taskmgr.exe) to open a new copy of Excel so you now have two running processes.  Each one can be used to open a different workbook (e.g. on different monitors) and contents can be copied back and forth.

Then: merging revision comments in Word

Perhaps you work in a team where instead of collaboratively editing one document, people each create their own versions with their own comments? Thankfully, Word 2010 (and probably other versions too) can merge the comments and changes into a single document. That single feature saved me hours this morning…

Finally: wildcards in Salesforce.com reports

My final tip from “Mark’s exciting week in pre-sales” (I jest) was gleaned whilst trying to create a report in Salesforce.com to show my team’s pipeline. I can’t rely on opportunities being correctly tagged, so I needed a report that used searches on a number of fields (and a filter to apply Boolean logic) but was picking up some false positives.  The problem was that one of the search criteria was also a partial match on some other results.  By changing the “contains” criteria from thing to thing*, I got just the results that started with “thing” and not the ones that included “thing” (like “something”).

That explanation is not as clear as I’d like, but I don’t want to spill the beans on some proprietary information – just take a look at the Salesforce.com advice for refining search using wildcards.

Formulas shown in Excel, instead of their results

Every month or so, I have to create a report which is extracted from SharePoint to Excel, and then a couple of extra columns are added with information required by a colleague. The additional columns simply present existing information in a different format but, for some reason, after pasting in these formulae (which are just nested IF statements combined with some string handling functions), the formula is shown instead of the calculated result:

=IF(LEFT(L2,1)=”A”,”Application”,(IF(LEFT(L2,1)=”B”,”Business”,(IF(LEFT(L2,1)=”I”,”Infrastructure”,”Error”)))))
=LEFT(L2,1)&”:”&RIGHT(LEFT(L2,SEARCH(“>”,L2,30)-2),LEN(LEFT(L2,SEARCH(“>”,L2,30)-2))-SEARCH(“>”,LEFT(L2,SEARCH(“>”,L2,30)-2)))

Last month, whilst troubleshooting the issue, I stumbled across a great blog post from Excel MVP, Purna Duggirala (aka Chandoo), entitled “Excel Formulas are not working?!? What to do when all you see is the formula, not result“. Today, I needed to find it again, so I thought I’d blog it this time!

Chandoo’s post lists a number of causes for this issue – mine was a simple issue of cell formatting – after pasting my formula into a new column, the cell is formatted as Text.  Switching the cell format to General, and editing the formula (no changes required, just F2 to edit, then Enter to commit), results in a calculation. Even better, Excel 2010 auto-fills and calculates the following rows for me!

The blog at Chandoo.org (tagline, “Become awesome at Excel”!) is packed with Excel advice and is definitely one to remember next time I’m having problems (or just want to do something a bit different in a spreadsheet).

Export to spreadsheet from SharePoint 2007 with Excel 2010

I frequently have to export data from a SharePoint list to Excel but earlier today I found it no longer works since I’ve upgraded to Office 2010.  Our SharePoint infrastructure is based on SharePoint 2007 and each time I attempted to Export to Spreadsheet from the Actions menu on the Toolbar, Excel would hang.

Luckily, I found Gustaf Lindqvist’s post on synchronising data between Excel 2010 and a SharePoint 2007 list. He suggests installing the 2007 Office System Driver Data Connectivity Components (I wasn’t getting an error message to help me find the problem – just a “Not Responding” application and the “doughnut of death”.

It’s still a bit flaky – and I’m not sure I have the complete answer (in fact, I suspect there may even be issues with some of the views in SharePoint) – but at least I can export data now…

Introducing Microsoft PowerPivot

I spent last week hanging out in West London at the Microsoft UK TechDays events in order to learn something more about the technologies I work with and something new about a few others.  Thursday’s SQL Server/Business Intelligence sessions definitely fell into the latter category and I saw some pretty cool stuff including PowerPivot.

Formerly codenamed Gemini, PowerPivot is part of the SQL Server 2008 R2 release but does not really need SQL Server.  It is intended to provide business end users with access to distributed data. Available for both Excel (2010 only) and for SharePoint, it lets Excel power users know where data is, how to get to it and need to share it.

PowerPivot allows users to pull in large quantities of data from disparate sources for fast analytics.  It can store hundreds of millions of rows of data in Excel and runs analysis services on the client, with data stored in memory – not SQL. Column-based compression is used to reduce the data size on disk (the actual ratio depends on whether that data is textual or numeric – numeric compresses well and that’s generally the sort of data that is used for this type of reporting). There is a limit of 4GB of address space and a 2GB file limit on disk but Microsoft state that’s not a limitation of PowerPivot – these restrictions mean that reports can be deployed to SharePoint later.

In order to make PowerPivot easy for business users to use, Microsoft has come up with a set of Data Analysis eXpressions (DAX) that provide a simplified set of analysis functions using Microsoft Excel-like syntax.  Business users can perform DAX functions and standard Excel calculations on the data, including the use of Excel slicers (a new feature so that end user do need to understand pivot tables in order to filter the data). Pivot tables, slicers and graphs can be built up to create a dashboard, which is just a few clicks away from being saved to SharePoint.

Once published, users can view a PowerPivot Gallery in SharePoint – including previews of reports, and so an existing report can be used as a data source for a new report using ATOM for the publishing/syndication.   Taking that a step further, PowerPivot for SharePoint allows pivots to be published as web applications for a team – and administrators can track usage of the dashboards that users create to discover those “apps” that are becoming business critical, in order to transition them to a state that is properly cleansed/governed. 

Whilst SharePoint is the only supported platform in order to publish PowerPivot reports (and obtain management data), the data consumed by the pivots may originate from a variety of sources.  It’s worth noting though that, if SharePoint is used, an Enterprise SharePoint platform is required in order to provide the Excel Services capability.

Whilst some are concerned about bringing together data from disparate sources, PowerPivot does not represent anarchy. The data may not have been cleansed (e.g. using fuzzy logic) but, if needs to be governed with proper stewardship, it can always be brought into the data warehouse. 

As for securing the data – if users can access the data, they can access it regardless of PowerPivot. Organisations should look to use rights management and other security mechanisms to protect data from information leakage.

In summary, PowerPivot allows:

  • Analysis of external data within the context of corporate data.
  • Analysis of large data sets beyond the limits of Excel.
  • Sharing of insights.
  • Consumption of reports as data sources.
  • Easy access to data, without a major IT project.
  • The ability to gather business requirements (e.g. identify commonly used reports) prior to implementing a fully managed reporting solution.

More details of PowerPivot can be found in the PowerPivotPro FAQ or at Microsoft’s PowerPivot site, where there are videos, demos, and the software can be downloaded for free.