Multiple IF statement with additional requirements

[Webbers]

I have a multiple IF statement, and it works quite well. But I am trying to "upgrade" it. I added an additional column to my worksheet. The current formula which works fine is:

=IF(NOT(ISBLANK(O198)),"PAID",IF(AND(ISNUMBER(D198),D198>30),TODAY()-D198,"Incomplete"))

but now I have Column C, which is a "status". The formula above is in cell V198. Column O is where the payment date is recorded. Column O is left blank until payment is received. Now in cell C198, there can be any of the following options (from a pulldown list):

Cancelled
Completed
Hold
Pending
Printed
Scheduled

Now in addition to what the current formula does, I also want it to allow the following.

If the value in Column C is then Column V would display
Cancelled Cancelled
Completed *use formula to determine if payment received in Column O.
Hold Incomplete
Pending Incomplete
Printed Incomplete
Scheduled Incomplete
(Blank) (Blank)

If payment has been received (payment date recordered in Column O, then PAID is the value shown in Column V (where this formula is). If there is no data in Column O AND Column C shows Completed and Column D shows a completion date, then the number of days between today and the completion date (Column D) is displayed. Otherwise, this cell shows Incomplete.

Does this make sense? I am not that famiar with the "NOT" function, and the ISNumber function. So as a result, I am having difficulty determining exactly how to incorporate this new set of criteria into my current formula. I have spent over 1 hour working on this, and I am no closer to figuring it out.

[shades]

One of the difficulties of the IF formula is the limits (yes, you can go beyond seven nested IF's, but it gets more complicated). I wonder if you set up a lookup table with the options, and then use a LOOKUP function to achieve what you want. Might be worth a try. (easier to trouble-shoot, as well).

[Webbers]

I wonder if you set up a lookup table with the options, and then use a LOOKUP function to achieve what you want.

I am not sure about that, as the formula is in all cells within the column. So for each row, the formula would reference the correct row. The formula I used as an example:

=IF(NOT(ISBLANK(O198)),"PAID",IF(AND(ISNUMBER(D198),D198>30),TODAY()-D198,"Incomplete"))

was from row 198 of course. So it references other cells within that row. I like the idea of using a table and lookup. But I am not sure how I could even begin to alter the formula when a specific cell reference is used. And of course as the row the formula is in changes, so do the specific clell references.

I was considered an expert at Excel where I worked, but that is only because they never knew about ya'll! I know I have so much to learn, and I am increasing my Excel knowledge daily trying to keep up with ya'll!

[Brandtrock]

Hey Webbers,

NOT simply reverses the logic of another logical evaluation. In this case, ISBLANK checks for a cell being blank, nesting within a NOT means we are testing for cells that aren't blank.

ISNUMBER tests to see if the cell contents are numeric.

I have attached a file with an amended formula that does what I think you need. If you get too many "new" tests, Shades suggestion will become a necessity.

HTH

[Webbers]

Thanks Brandtrock! That formula worked like a dream to accomplish my goal!