Have you tried using a regular expression which should catch all occurrences of
"State?" In Oo Help, search for regular expressions to see how it should be
written. Then, you should be able to add the regular expression to your IF
formula. You can't use a wildcard such as "* or ?" in a formula (to the best of
my knowledge).
Scott Castaline <[EMAIL PROTECTED]> wrote:
JosephK wrote:
> On 14:12 Sun 29 Jul , Ken Burnside wrote:
>>> =IF(C16="State Funds";135;0)
>>>
>>> The determining factor is really simple, in all cases where the text in
>>> say C16 contains State H16 = 135 otherwise H16 = 0. The determining
>>> field could contain just State Funds which is the majority of the time,
>>> or can also contain State Funds/Peachcare or State Funds/Wellcare. What
>>> is the proper way in the formula to tell calc that if the C16 contains
>>> State, and the above variations mentioned that H16 should be 135,
>>> otherwise put 0 into that cell.
>> The answer to your first question is this:
>>
>> =IF(C16="State Funds/Peachcare";135;IF(C16="State Funds/Wellcare";135;0))
>>
>> For each permutation of "State Funds", you need to have a nested IF
>> statement. I do not know if OpenOffice Calc has a hard limit on the
>> number of nested IF statements allowed; Excel will only allow you to
>> go to 7 levels of Nested Ifs.
>>
>> If you need more than nested IFs, look into VLOOKUP and HLOOKUPs,
>> which are very powerful tools to let you treat a named range of cells
>> as a flat database.
>>
>
> I thought this might be more easily solved using regex's so I tried
> =IF(C16="State.*";135;0)
>
> Regex's are enabled but this did not work for either C16=State or
> C16=State Funds. .* should match zero or more occurrences of any
> character. Is this a bug?
>
> Regards, Joseph
I had originally tried the * and found that it didn't work. I haven't
tried in (cough, cough) Excel, so I'm not sure if that's an OOo defect
or is also in other Spreadsheet apps.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------
Ready for the edge of your seat? Check out tonight's top picks on Yahoo! TV.
