Vary formula based on what another cell contains
 

Hello

I have a spreadsheet where I would like to create a formula that says:

If cell.Offset(0, -2) contains "with GST", then formula = "cell.Offset(0,
-1)/8".
Elseif cell.Offset(0, -2) contains "without GST", then formula = "0"
Elseif cell.Offset(0, -2) contains "ZR", then formula = "0"

I'm not sure how to write the formula to look for something *contained* in
the target cell, rather than checking the entire cell contents. Any help
would be greatly appreciated.

Thanks
MMH

Vary formula based on what another cell contains
 

the operator is "Like"

* = none or many characters
? = exactly one char

"toto" Like "*ot*" is true

find more details in the vba help, but the idea is to use
T$ Like "*wanted*"

and this will be true since wanted is include in T

Vary formula based on what another cell contains
 

Whether the cell contains "without" or "ZR" the result is the same.
So I hope that the term "with GST" is at the and of the cell.
Then the following formula will work.

=IF(RIGHT(A1,8)="with GST",B1/9,0)

Seen the lack of other responses I presume it is hard to check for any
containment.

--
Greetings from New Zealand
Bill K
"MMH" wrote in message
...
Hello

I have a spreadsheet where I would like to create a formula that says:

If cell.Offset(0, -2) contains "with GST", then formula = "cell.Offset(0,
-1)/8".
Elseif cell.Offset(0, -2) contains "without GST", then formula = "0"
Elseif cell.Offset(0, -2) contains "ZR", then formula = "0"

I'm not sure how to write the formula to look for something *contained* in
the target cell, rather than checking the entire cell contents. Any help
would be greatly appreciated.

Thanks
MMH

Vary formula based on what another cell contains
 

Oops,

I have always worked with the divide by 9 in stead of 8 to find the amount
of gst out of a gross amount.
Your example showed divide by 8.
Greetings
Bill K

"Bill Kuunders" wrote in message
...
Whether the cell contains "without" or "ZR" the result is the same.
So I hope that the term "with GST" is at the and of the cell.
Then the following formula will work.

=IF(RIGHT(A1,8)="with GST",B1/9,0)

Seen the lack of other responses I presume it is hard to check for any
containment.

--
Greetings from New Zealand
Bill K
"MMH" wrote in message
...
Hello

I have a spreadsheet where I would like to create a formula that says:

If cell.Offset(0, -2) contains "with GST", then formula = "cell.Offset(0,
-1)/8".
Elseif cell.Offset(0, -2) contains "without GST", then formula = "0"
Elseif cell.Offset(0, -2) contains "ZR", then formula = "0"

I'm not sure how to write the formula to look for something *contained*
in
the target cell, rather than checking the entire cell contents. Any help
would be greatly appreciated.

Thanks
MMH