PDA

View Full Version : Solved: need little advice on this Formula



mercmannick
04-17-2006, 12:38 PM
=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
04-17-2006, 12:46 PM
=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
04-17-2006, 12:58 PM
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
04-17-2006, 01:02 PM
Maybe..


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

??

Bob Phillips
04-17-2006, 01:06 PM
Guessing as the conditions do not seem clear



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

mercmannick
04-17-2006, 01:14 PM
=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
04-17-2006, 01:19 PM
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
04-17-2006, 01:21 PM
=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
04-17-2006, 01:24 PM
"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
04-17-2006, 01:35 PM
Maybe then..



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

Bob Phillips
04-18-2006, 01:17 AM
=IF(AND($S$17>=$T$19-50,$S$17<=$T$19),"A",IF($S$17<$T$19,"R","G"))

mercmannick
04-18-2006, 09:33 AM
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
04-18-2006, 02:51 PM
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
04-19-2006, 09:20 AM
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
04-19-2006, 02:53 PM
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
04-19-2006, 05:09 PM
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
04-20-2006, 08:38 AM
thanks both of you works a charm

:)

Merc