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 -- It is often safer to be in chains than to be free. Joseph K --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
