Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

If cell contains certain text then run this formula, otherwise run this formula instead

Status
Not open for further replies.

pendle666

Technical User
Jan 30, 2003
295
GB
Hello

G5 contains a code - could be anything but if there is a K in that code then I want, cell C19 to run a formula: value in G6 divided by 12 multiply by -1 to give a negative figure. But if there's a K in G5 then I want just G6 divided by 12.


What I have at the moment is:

=IF(G5="*K*",G6/12*1,G6/12*-1)

I've had a look round and there is ISERROR, SEARCH etc which will return True and False, but I want a math result. The asterisk for wildcard isn't working for me.

Can you tell me where I've gone wrong?



thank you for helping

____________
Pendle
 
I don't think you can write a formula in a cell that puts something in other cell.

You could accomplish this with a little of VBA code...

---- Andy

There is a great need for a sarcasm font.
 
Okay thanks - no worries, I was just trying to save a bit of time, I can learn to look for the K and change the result appropriately!!

thank you for helping

____________
Pendle
 
How about using the FIND() function? Not at my PC, but...
[tt]
=G6/12*IF(FIND(G5,”K”)>0,1,-1)
[/tt]

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
If G5 contains "K" then this (Skip's) formula works (flipped around Blue parts):
[tt]=G6/12*IF(FIND([blue]"K", G5[/blue])>0,1,-1)[/tt]
else, (no "K" in G5) the formula returns [tt]#VALUE![/tt] :-(



---- Andy

There is a great need for a sarcasm font.
 
[tt]
=G6/12*IF(IFERROR(FIND("K", G5),0)>0,1,-1)
[/tt]

Kind of reminds me of...
[tt]
e[sup](pi * i)[/sup] + 1 = 0
[/tt]

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top