Final Friday Fix: August Challenge

Working with Multiple Criteria and Text

If you work with Excel in finance, sometimes your data integrity is not as good as you might hope. You may have omissions, duplicates or errors and sometimes, it may contain text. And that last issue can be a real pain in its own right. Let me explain with this month’s challenge…

The Challenge

Today we are all going to be bankers and you have been presented with the following report (you can download this example here):

Let me explain the table.  Essentially, we have two businesses, A and B, who each have standard and deluxe bank accounts (presumably the latter has money in it!).  Cell E17 shows that business A has 117 standard bank accounts for example.

Now here at the bank, we have identified the average time it takes each month to deal with each account (so for instance cell H8 shows each of B’s deluxe accounts takes 1.5 hours to process).

Finally, row 11 shows the hourly rates for each process (so cell G11 denotes that interview time is $24 per hour).  Therefore, total costs will be (117 x 3.4 x $15) + (117 x 3.0 x $24) + (117 x…

You should note a problem here though.  See the lovely shade of red in cell H11?  That’s because we don’t have the data presently and rather than leave the cell blank some kind soul has been “helpful” and typed in “n/a” instead.  Lovely.

So here’s the challenge.  Without adding any helper cells, rows or columns, or using VBA / user-defined functions, can you create a scalable formula which will ignore text and still calculate the total costs associated with business B’s deluxe accounts?

For the record, “scalable” means I could add more processes, more types of account and more businesses.  Therefore, the following “simplistic” formula

might be a solution presently, but (a) won’t scale and (b) is absolutely horrible.

Sound easy? Try it. We’ll publish our suggested solution in Monday’s blog. Have a great weekend!