+ Reply to Thread
Results 1 to 5 of 5

Value of cell formula not working in nested IF formula.

  1. #1
    Registered User
    Join Date
    10-26-2015
    Location
    Santa Barbara,CA
    MS-Off Ver
    Office 365 Pro
    Posts
    3

    Value of cell formula not working in nested IF formula.

    I'm trying to create a sheet that will calculate attendance points based on the day the point(s) were issued and todays date. One point will drop off every 60 days and the max points given in a day is 4. I have it mostly figured out except one of my reference cell values is a formula pulling from a vlookup referencing a drop-down and seems to break the points formula. I'll try to explain best I can.
    Cell M1 has the formula =TODAY() to give me the current date
    Cell D2 has the formula =IF(C2<>"",VLOOKUP(C2,$K$1:$L$10,2,),"") which is simply providing the number of points given for a specific infraction in a drop-down list.
    Cell A2 has the date the points were issued.
    Cell F2 is where I am trying to calculate the point balance based off of the number that was issued and current date using the formula below. If I put a number in D2, the formula works but with the formula in D2 it does not.

    =
    IF((AND($M$1-A2>60,$M$1-A2<120,D2>=1)),D2-1,
    IF((AND($M$1-A2>=120,$M$1-A2<180,D2>=2)),D2-2,
    IF((AND($M$1-A2>=180,$M$1-A2<240,D2>=3)),D2-3,
    IF((AND($M$1-A2>=240,D2>=4)),D2-4,
    D2))))

    Any assistance would be greatly appreciated.

    D

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,929

    Re: Value of cell formula not working in nested IF formula.

    Your formula can be shorted to this...
    =D2-IF($M$1-A2>240,4,IF($M$1-A2>=180,3,IF($M$1-A2>=120,2,IF($M$1-A2>=60,1,0))))

    I could probably work out a formula to use a rounddown((M1-A2)/60) but that seems to do the trick
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    10-26-2015
    Location
    Santa Barbara,CA
    MS-Off Ver
    Office 365 Pro
    Posts
    3

    Re: Value of cell formula not working in nested IF formula.

    FDibbins, this is great except it's eventually showing negative numbers when starting points are less than 4.

  4. #4
    Registered User
    Join Date
    10-26-2015
    Location
    Santa Barbara,CA
    MS-Off Ver
    Office 365 Pro
    Posts
    3

    Re: Value of cell formula not working in nested IF formula.

    Disregard last. Looks like a simple MAX addition does the trick. Thank you much for the help.

    =MAX(D2-IF($M$1-A2>240,4,IF($M$1-A2>=180,3,IF($M$1-A2>=120,2,IF($M$1-A2>=60,1,0)))),0)

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,929

    Re: Value of cell formula not working in nested IF formula.

    Good work-around

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Nested If and in array formula not working
    By dsrt16 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-24-2018, 07:49 PM
  2. [SOLVED] nested 'if' formula not working
    By couscraig in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-26-2016, 01:07 PM
  3. [SOLVED] Formula with IF and nested AND not working
    By Nights in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-21-2015, 04:28 PM
  4. IF formula nested not working
    By certain_death in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-01-2014, 05:36 PM
  5. IF( AND( NESTED formula is not working
    By genetist in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-01-2014, 01:54 AM
  6. Replies: 3
    Last Post: 03-25-2014, 06:55 AM
  7. Nested IF formula partially not working
    By Mattacola in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-14-2012, 03:21 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1