EXCEL: If-then statements

[pologoalie8908]

I have a row of data: A1=ticketnumber B1=priority(low,med,high) C1=description D1=persons name E1=orginizations F1=last modified G1= date reported H1=Todays date J1=days open K1= days since last update

i basilcy want B1 to change names from low,med,high based on the values in J1. So if J1 is higher than 10, i want B1 to change automaticly to the word medium, and if j1 is greater than 30 to automaticaly say high

please help

 

[getochkn]

=IF(AND(B1>10,B1<30),"med",IF(B1>30,"high","low"))

 

[USAFRet]

=IF(J1>30,"High",IF(J1<11,"Low","Medium"))

Nested IF. Learn it, love it.

 

[pologoalie8908]

OK well the words in B are reported from a web serivce i use and i want them to stay like that UNLESS "J" gets to 10 and 30. So B might have the word medium already, but i dont want to apply that formula becqause it will change it to low

 

[getochkn]

Sorry forgot to add: I dont want the words in "B"replaced if "J" is less than 10. Since the wrods in B are there from a copy pasted there before formating is applied

Can't really be done that way, because column B has to have the above formula in it (mine or USA's, both work the same, different approach) in the first place to calculate the answer to show in B. Where would you put this formula then if it's not in B?

 

[pologoalie8908]

Sorry forgot to add: I dont want the words in "B"replaced if "J" is less than 10. Since the wrods in B are there from a copy pasted there before formating is applied

Can't really be done that way, because column B has to have the above formula in it (mine or USA's, both work the same, different approach) in the first place to calculate the answer to show in B. Where would you put this formula then if it's not in B?

in J?

 

[getochkn]

Sorry forgot to add: I dont want the words in "B"replaced if "J" is less than 10. Since the wrods in B are there from a copy pasted there before formating is applied

Can't really be done that way, because column B has to have the above formula in it (mine or USA's, both work the same, different approach) in the first place to calculate the answer to show in B. Where would you put this formula then if it's not in B?

in J?

J has the value, 10,20,30, whatever. B has a word, low, med, high. You need the formula "=IF(AND(B1>10,B1<30),"med",IF(B1>30,"high","low"))" in column B in order to change the words in B. you can't put this formula anywhere and have it change B. The worksheet functions can only return a value, not set it.

What you want is programming done in VBA programming. This would let you write a function that would change the values of B based on any cell, etc.

I could do it, but it seems like this is for your work, and no offence, but I'm not getting paid to do this, so I don't have the time to write, debug, etc an entire VBA function to do this. I think you need some Excel training if they are expecting you to do this.

Alternative would be to not change the words in the cell, but you conditional formatting to change the color of the cells in column B based on a value in column J. It wouldn't change the words but you could do, change background to green if J< 10, yellow if it's between 10 and 30 and Red if it's over 30, or whatever, so you can visually look and see, these ones are RED, they are over 30, etc.

 

[USAFRet]

OK well the words in B are reported from a web serivce i use and i want them to stay like that UNLESS "J" gets to 10 and 30. So B might have the word medium already, but i dont want to apply that formula becqause it will change it to low

Then the problem needs to be defined more. B already having other data complicates things.
What you're looking for is the word "Sometimes". As in, "I want it to always show this, but sometimes I don't"

And as with getochkn, I ain't getting paid for this.
Put that formula in another column....X...and ignore the web service derived value in B.

 

[getochkn]

Gave yours the best answer, was a bit more slick than mine and avoided the AND. Both did the same thing but I like streamlined code when possible and yours was it.

Also, since the OP seemed to not like our answers about doing his work for him. lol. I've done custom Excel macro work for $100/hour for companies before. I'll point in the right direction to someone on here, but without that $100, I have better things to do than do their job and write the whole thing for them.