Issue with % Greater Than Formula

[GreedySheedy]

Hi Guys,

I've been meaning to sign up to the site for a while and have finally encountered a problem which no amount of Googling helped, so I thought there's no better time to sign up!

Anyway, onto the issue.

It's a tough one to explain but, I'm using a combination of IF statements, vlookups, conditional formatting and Wingdings 3 to create a dynamic spreadsheet based on a few data validation lists. I have one row which seems to suggest that 7.79% is greater than 21.39%, and this is resulting in my conditional formatting/wingdings 3 set-up not working. What is strange is that the formula works perfectly fine when comparing percentages over 9.99%. See below;

The formula in cell AD18 (where my green arrow issue is, is the following;

=IF(ISBLANK(AC18),"",IF(OR(AC18="TBC",AC18="N/A"),CHAR(6),IF(OR(AND(Z18="TBC",AC18<>"TBC"),AND(Z18="N/A",AC18<>"N/A")),"p",IFERROR(IF(AC18>Z18,"p",IF(AC18<Z18,"q",IF(AC18=Z18,"tu",""))),"N/A"))))

Basically, if AC18 is less than Z18, then down arrow is the result I want from this calculation. It works for all the other cells apart from when the percentage is 3 digits.

For reference the formula in cell AC18 is;

=TEXT(VLOOKUP($B18,Data!$B$10:$Q$15,AC$1,0),IF($C18="£","£#,###",IF($C18="%","0.00%",IF($C18="//","0.00","###"))))

I found that if I use the Value formula around this, i.e. changing the 7.79% to 0.0779, it works. Equally, if I change the IF($C18="%","0.00%" element in the above formula to IF($C18="%","00.00%" then it also works, although neither of these are desirable, and the first 'solution' actually messes up some of the other arrows.

I know it's probably a simple issue but I cannot figure out why this is happening. Perhaps something to do with the % formatting in the formula, or how it's treating the cell value, but all the formats are the same (all are 'General'), so again, I am at a loss to explain why it is doing this.

Any help would be greatly appreciated!

Cheers,
Greedy.

 

[Eric W]

Welcome to the MrExcel forum!

Your problem is that you're using TEXT to format your results, so the values in AC18 and Z18 are text values, not numbers. The rules for comparing text values are different than comparing numbers. In your example, 7.79% is greater than 21.39% because "7" is greater than "2" (looking at the first character of each text value).

Your best bet is to store all your numbers as numbers, and use cell formatting to display the numbers as you wish (percentage, with 2 decimals). Then the numeric comparisons should work as you expect.

 

[GreedySheedy]

That's great, thanks for the reply (and warm welcome) Eric.

One question I would have is, I want this spreadsheet to be dynamic, i.e in row 6, 8,10 for example, I might want it to display a metric that is a % or possibly select a metric that is a number with only 1 decimal. This is why I used the TEXT formula to specify what the format was so it made sense to in relation to the metric (although wasn't really aware of the limitations). In this case, is there anyway to convert a number to % format without either the TEXT formula or VBA?

 

[Eric W]

I'd think that's where Conditional Formatting would come in. If you have a formula in those cells, and it could return a different type of number (percent, amount, count, etc.) based on some condition somewhere, you could use CF to apply the appropriate number format by looking at the same condition. But whatever type of number it is, it should really stay a number. If you really want to format it as TEXT, at least use a text format with leading zeros, so that all the numbers you're comparing have the same length But I think you'd still have issues.