Solved: need little advice on this Formula

[mercmannick]

=IF($S$17<$T$19,"R",IF($S$17>$T$19,"G","A"))

i need to be able to IF($S$17>$T$19,"G",) to add to this if $S$17 = betwen $T$19 and 50 then if true "A"

Hope this makes Sense

Merc

:doh:

[Zack Barresse]

=IF($S$17>$T$19,"G",IF(AND($S$17<50,$S$17>$T$19),"A",""))

.. although you didn't say what you wanted if all were False, so I left it "" null.

[mercmannick]

firetyr

first part of formula looks at S17 if it is less than the value in T19 then put a R
second part if S17 is greater than T19 then put a G

what i am after trying to acheive as well in same formula is "IF S17 = T19 or no less than 50 of S17 then put a A

it is all for a RAG status report i am running

(S17 is the Target, T19 is the output so far)

Merc

[Zack Barresse]

Maybe..


=IF(S17>T19,"G",IF(S17<50,"A",""))

??

[Bob Phillips]

Guessing as the conditions do not seem clear



=IF(OR(S17=T19,S17>=50),"A",IF(S17>T19,"G","R"))

[mercmannick]

=IF($S$17<$T$19,"R",IF($S$17>$T$19,"G","A"))

is the formula at the moment

after the second IF IF($S$17>$T$19,"G" i need to determine if $S$17 is inbetween 50 and the Total in T19 then i will class it as "A" amber , because it is nearly T19 but only short of no less than 50

Merc

[Zack Barresse]

I don't see the need for the second IF clause, if the "S17<T19" calculates to false it's going to the false statement anyway, no need to check it again. Your logic is not too clear here.. Did you try my last formula? What is it about that which does not work for you?

[Bob Phillips]

=IF($S$17<$T$19,"R",IF($S$17>$T$19,"G","A"))
is the formula at the moment

after the second IF IF($S$17>$T$19,"G" i need to determine if $S$17 is inbetween 50 and the Total in T19 then i will class it as "A" amber , because it is nearly T19 but only short of no less than 50

Merc

But if S17 is between 50 and T19 that means it will be less than T19, which means it is red.

[mercmannick]

"A" is going to be true if it is no less than <=T19 by 50

So if T19 value is 250 and S17 is value of 200 - 249 then "A"

if S17 is 250 or more "G"

If S17 is less than 200 then "R"

Merc

[Zack Barresse]

Maybe then..



=IF(S17-T19<=50,"A",IF(S17-T19<=200,"R",IF(S17-T19<=250,"G","")))

[Bob Phillips]

=IF(AND($S$17>=$T$19-50,$S$17<=$T$19),"A",IF($S$17<$T$19,"R","G"))

[mercmannick]

xld

adjusted your formula to suit what i need thnk you

=IF(AND($T$19>=$S$17-50,$T$19<=$S$17),"A",IF($S$17<$T$19,"R","G"))

Merc

[Bob Phillips]

xld

adjusted your formula to suit what i need thnk you

=IF(AND($T$19>=$S$17-50,$T$19<=$S$17),"A",IF($S$17<$T$19,"R","G"))

Merc

Okay, a little dyslexia :dunno

[mercmannick]

Okay, a little dyslexia :dunno

Hehe np

Now my Team are asking is it possible to have this done on a % :(

lets say A1 = target
B1 = current output

if B1 = 0 - 70% of A1 then R

if B1 = 71- 90% of A1 then A

if B1 = 91 - +% of A1 then G

would this be at all possible ?

:banghead:

Merc

[Bob Phillips]

Hehe np

Now my Team are asking is it possible to have this done on a % :(

lets say A1 = target
B1 = current output

if B1 = 0 - 70% of A1 then R

if B1 = 71- 90% of A1 then A

if B1 = 91 - +% of A1 then G

would this be at all possible ?

:banghead:

Merc

=VLOOKUP(B1/A1,{0,"R";0.71,"A";0.91,"G"},2,TRUE)

[Shazam]

Hehe np

Now my Team are asking is it possible to have this done on a % :(

lets say A1 = target
B1 = current output

if B1 = 0 - 70% of A1 then R

if B1 = 71- 90% of A1 then A

if B1 = 91 - +% of A1 then G

would this be at all possible ?

:banghead:

Merc


xld provided the answer but here another way of doing it.

=LOOKUP(B1/A1,{0,0.71,0.91},{"R","A","G"})

[mercmannick]

thanks both of you works a charm

:)

Merc