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]
