Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: Multiple IF statement with additional requirements

  1. #1
    Join Date
    10th September 2003
    Location
    Orlando, FL
    Posts
    129

    Multiple IF statement with additional requirements

    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.
    Sherry
    Orlando, FL

  2. #2
    Join Date
    11th February 2003
    Location
    Near the Land of OZ
    Posts
    1,591
    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).

    Software: OpenOffice 3.0/NeoOffice 3.0 on Mac OS X 10.5.6
    Humanware: Older than dirt

    Old, slow, and confused - but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3.28.2008)

  3. #3
    Join Date
    10th September 2003
    Location
    Orlando, FL
    Posts
    129
    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!
    Sherry
    Orlando, FL

  4. #4
    Join Date
    20th May 2003
    Location
    Mesa, Az
    Posts
    1,110
    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
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

  5. #5
    Join Date
    10th September 2003
    Location
    Orlando, FL
    Posts
    129
    Thanks Brandtrock! That formula worked like a dream to accomplish my goal!
    Sherry
    Orlando, FL

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 15
    Last Post: July 5th, 2006, 23:14
  2. multiple if then statement?
    By shiggles in forum EXCEL HELP
    Replies: 1
    Last Post: June 11th, 2006, 08:34
  3. Countif on multiple requirements
    By grays in forum EXCEL HELP
    Replies: 2
    Last Post: September 12th, 2005, 16:48
  4. Multiple IF statement
    By robolsson in forum EXCEL HELP
    Replies: 3
    Last Post: April 6th, 2005, 06:43
  5. Multiple If Statement??
    By ConcreteMonkey in forum EXCEL HELP
    Replies: 6
    Last Post: October 30th, 2004, 01:20

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno