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

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.

This entry was posted in How to and tagged , . Bookmark the permalink.

3 Responses to Using a single column for percentages and currency amounts

  1. Anonymus says:

    Can you please tell me how to convert percentages to money representation($) like dime and quarter.
    How much is 66.6% in money representation?

  2. Sabrina says:

    It worked! Just what I was looking for. Thanks.

Comments are closed.