formula

[Kris]

I am trying to write a formula that will look at another cell and populate an
answer depending on what is in the other cell.

For instance: in cell D28 is either a number from 1 to 10 or the letters TBD.

I want excel to look at that cell and if the number is from 8 - 10 I want it
to populate "Successful" in cell D29

if the number in D28 is between 6 - 7.9 I want it to populuate "Needs
Improvement" in cell D29

if the number in D28 is less than 6 I want it to populate "Unsuccessful" in
cell D29

If it states TBD in cell D28 I want it to populate TBD in cell D29

I was successful in writing the formula for the range of numbers, but the
twist seems to be the TBD part.

So far my formala is like this:

=IF(D14>8,"Successful",IF(D14>6,"Needs Improvement",IF(D14>0,"Unsuccessful")))


Any suggestions on adding the TBD part to this formula?
Thanks

 

[Bernie Deitrick]

Kris,

=IF(D14<>"", IF(D14="TBD","TBD",IF(D14>8,"Successful",IF(D14>6,"Needs
Improvement",IF(D14>0,"Unsuccessful","Invalid")))),"")

 

[Kris]

Thanks Bernie, this did work, but can you explain what the first part of the
formula you typed in is doing?

I am referring to the =IF(D14<>"",

Thanks!
Kris

 

[Bernie Deitrick]

Kris,

It checks to see if the cell is blank, and if it isn't, it checks for the other values - if it is
blank, it returns a blank.

There are other checks that you could add - for example, if you mis-type TBD as TDB, you will get
"Successful" - so you could add a check for that as well:

=IF(D14<>"", IF(D14="TBD","TBD",IF(NOT(ISNUMBER(D14)),"Invalid
String",IF(D14>8,"Successful",IF(D14>6,"Needs Improvement",IF(D14>0,"Unsuccessful","Invalid
Number"))))),"")

HTH,
Bernie
MS Excel MVP

 

[Kris]

ok, that helps, but let me add another twist to it.

Suppose I only want it to calculate "Successful" if the value is between 8
and 10, if someone types in any value over 10, I want it to show invalid, can
I add this as well?

 

[Bernie Deitrick]

Of course! Like so:

=IF(D14<>"", IF(D14="TBD","TBD",IF(NOT(ISNUMBER(D14)),"Invalid String",IF(D14>10,"Too
High!",IF(D14>8,"Successful",IF(D14>6,"Needs Improvement",IF(D14>0,"Unsuccessful","Too
low!")))))),"")

HTH,
Bernie
MS Excel MVP

 

[Kris]

Thanks Bernie, it worked like a charm!

Of course! Like so:

=IF(D14<>"", IF(D14="TBD","TBD",IF(NOT(ISNUMBER(D14)),"Invalid String",IF(D14>10,"Too
High!",IF(D14>8,"Successful",IF(D14>6,"Needs Improvement",IF(D14>0,"Unsuccessful","Too
low!")))))),"")

HTH,
Bernie
MS Excel MVP