Mexican Projects!

During my week in Mexico City last week, I was asked to work on various problems and projects by my delegates:

• how to get rid of the space between two words in a cell
• how to write a formula that will find the latest price in the rows of a table, even if that price is followed by a zero
• how to communicate the old and new version of holiday and total pay for an organisation
• using conditional formatting and a conditional chart for use in a dashboard

I did one and three by myself and got help with number two. Here are the details.

Concatenate with a Space

Imagine you have a list of names or products or streets … in a list in a work sheet, such as

Anne Williamson
Duncan Williamson
Trevor Bayliss

and you want to see

AnneWilliamson
DuncanWilliamson
TrevorBayliss

Look at the following screenshot to see how it’s done:

 

Oh! Wait a minute: accidentally, perhaps, there were two spaces between trevor and bayliss and, as intended, the formula only deleted one space, leaving the second space alone. You would need to adjust the formula to eliminate the extra space; this change to the formula does just that =LEFT(A3,FIND(”  “,A3)-1)&RIGHT(A3,LEN(A3)-FIND(”  “,A3)-1)

Find the Last Price in a Row of Prices

The second problem I was given was to work out how to find the latest price in a row of prices: for example, product BBBBB has had several price changes over the last six months and every time the price changes, the table of prices is updated, along with the prices of all other products. If one product changes price but others do not, these other products have a 0 (zero) entered in that latest column. This means, the table of prices can look like this:

The price of AAAAA has not changed at all in this section of the table; Product BBBBB has changed every period as has CCCCC, product DDDDD hasn’t changed at all until July 12 … and so on, as this table extends until July 2013. What we need, then, is a formula that will return the latest price for AAAAA, BBBBB … £0, £4.99 (august 2012) …

I got the help of Laurent Wright and Rory Archibald on the Excel-G discussion list and here are their solutions:

Laurent gave me the ARRAY entered =INDEX(D5:U5,1,MAX(IF(D5:U5<>0,COLUMN(D5:U5)-3,0))) … please note, that this formula is column specific which means that the -3 near the end of the formula will change if, for example, you were to add a new column A or B since this formula is in column C at the moment … add a column in front of column C and -3 becomes -4 … So, ARRAY enter the formula in cell C5 in this case then drag it down to the end of the list

Rory gave me the vector entered =LOOKUP(1E+100,1/C5:AAA5,C5:AAA5) … enter the formula in cell B5 and drag it down to the end of the list.

Note: Rory’s formula will allow us to add new columns to the end of this table up to column AAA whereas Laurent’s formula has to stop at the end of the table as it is at the time you program it. I have, however, not shown here, changed the table of prices to an Excel Table in an attempt to get Laurent’s formula to update automatically as new prices are added but so far without success!

Communication of a Scenario

I often talk about the communication of data and information in the context of any work we might do when using Excel. This third project was presented as a before and after scenario. The company which employs the delegate in question has changed its holiday pay arrangements and he wanted to know if he was better or worse off under the changes. The worksheet I was given turned out to be as accurate as I could have made it but, in my opinion, it was a bit of a headache to use. As I accepted the challenge I told my delegate that it might be that all I do is suggest a different layout. Here are the before and after with the before being difficult to show here in a truly sensible way, showing the old system, the new system and the differences between them!!

Before

After: this is what I recommended

Note: with this new layout, you can see the input section quite clearly and the output section sits right beneath, cascaded to the right. Moreover, all that we need to do to update the input and output here is to enter the daily salary and the years of seniority: the calculation table does the rest. That’s right, change two cells and the programming does the rest!

I also added to graphs that I thought would be useful: they are based on the before table and show the two systems side by side: so there can be a use for the large, original table!

There you are! I always come back with something and these were three mini projects that I thought you’d like to read about. Because of confidentiality I cannot share the Excel files with you beyond what you see here, sorry!

A Dashboard using Conditional Formatting and Chart

How can we get from here …

To here … ?

I will leave this one hanging: use conditional formatting to create the icon set solution to the top part of the wrok sheet: trend data and the icon set; and a conditionally formatted chart in which every bar is shown automatically as a different colour … no cheating, program it properly.

Then again, how about this very efficient and succinct suggestion?

 

Here is the Excel file for this final example for you to work through and discover! condit_format_and_chart

Duncan  Williamson