GreedySheedy
New Member
 Joined
 Sep 21, 2020
 Messages
 2
 Office Version

 2013
 Platform

 Windows
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 setup 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.
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 setup 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.