Please could someone kindly check my formula: trying to show a number 1 when I have less then 100%; to show 2 when I am equal to or over 100% and finally a three when the percentage exceeds 111%. the first two work but cannot show the required number 3 for the final part of the sum....am sure it is something simple.

Many thanks

=IF(G4="","",IF(G4<100%,1,IF(G4=100%,2,IF(G4=111 %,3))))

Hi,

Am Mon, 16 Jun 2014 11:50:31 +0100 schrieb revpeachy:

=IF(G4="","",IF(G4<100%,1,IF(G4=100%,2,IF(G4=111 %,3))))

try:
=IF(G4="","",VLOOKUP(G4,{0,1;1,2;1.11,3},2,1))
or
=IF(G4="","",IF(G4<1,1,IF(G4<1.11,2,3)))


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi again,

Am Mon, 16 Jun 2014 15:09:36 +0200 schrieb Claus Busch:

=IF(G4="","",IF(G4<100%,1,IF(G4=100%,2,IF(G4=111 %,3))))

the conidtions will be calculated in the order of appearance in your
formula. And if a condition is true you get the expected output and
calculation ends.
If you write = then you have to start with the largest value. E.g, 115%
is larger than 100%. So you get 2 as result.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

"revpeachy" wrote:
Please could someone kindly check my formula:
[....]
=IF(G4="","",IF(G4<100%,1,IF(G4=100%,2,IF(G4=111 %,3))))

Conditions are tested left-to-right. If G4 is larger than 111%, it is also
larger than 100%, which is tested first. So the formula would return 2
instead of 3 as you intended.

At a minimum, try:

=IF(G4="","",IF(G4<100%,1,IF(G4<111%,2,3)))

More generally, especially if you might have more conditions to test in the
futu

=IF(G4="","",LOOKUP(G4,{0,1,1.11},{1,2,3}))

That assumes G4 is never negative. If it might be, change 0 to -1E300,
almost the smallest negative number that can be represented and that is easy
to remember.

However, in all cases, you might encounter some surprises if G4 is a
calculated. It might display 100% or 111%, but that is rounded from the
actual value, which might be less.

The following is more reliable:

=IF(G4="","",LOOKUP(ROUND(G4,2),{0,1,1.11},{1,2,3} )

Change ROUND(...,2) to ROUND(...,4) if you format G4 as Percentage with 2
decimal places, for example.