Using a single column for percentages and currency amounts

This morning I received a slightly quirky requirement. The client had a spreadsheet containing people's salaries—a row per person—and wanted to be able to input either a percentage increase or an uplifted salary, and for a separate column to show the uplift amount, in pounds.

But because the spreadsheet was already quite wide (lots of columns being used), she wanted the percentage increase or the uplifted salary to be entered in a single column. So for someone with a salary of £30,000 in column C, say, she wanted to be able to enter in column D either the figure 20% or the figure £36,000, and for column E to automatically report £6,000 (the uplift amount). For the sake of explanation, let's assume that this person's data is sitting in row 2.

There were two challenges here. The first was to format column D correctly based on its contents. The next was to ensure that the formula used to calculate column E catered for the two possible values.

Formatting

To format the cell correctly, I assumed that no one would ever get a salary uplift in excess of 100%. (A safe assumption, I hope!) The Conditional Formatting feature in Excel 2007 and beyond has been enhanced such that as well as changing the aesthetics of a cell (its borders, background colour, text colour etc.), you can also change its display format.

I formatted the column as a number with zero decimal places, to represent salaries. I then applied conditional formatting to check for numbers less than or equal to 1 and format these as percentages.

Conditional format: cells less than or equal to 1

This would ensure that if the client entered 30% or 0.3, it would display it as 30%. And if she entered £36,000, it would show £36,000. Remember: while percentages are formatted as percentages, they are stored in Excel as decimals. So 30% is actually stored as 0.3. So the "<=1" check is looking for cells containing a percentage <=100%. Click here for a post all about percentages.

Logic

The next job was to populate column E. I used the following formula to achieve this.

=IF(D2<=1,D5*C5,D5-C5)

This looked in column D to see if it was less than or equal to 1. If it was, then it must be a percentage, so it multiplied the percentage by the base salary to get the uplift amount—in our example, 20% * £30,000, or £6,000. If this condition failed (i.e. a number greater than 1 had been entered), then it would simply subtract the old salary from the new one—in our example, £36,000-£30,000.

• Tweet