+ Reply to Thread
Results 1 to 13 of 13

Find the date of a variance amount over a limit and count the days since the variance.

  1. #1
    Forum Contributor
    Join Date
    12-15-2005
    MS-Off Ver
    Office 2007
    Posts
    346

    Find the date of a variance amount over a limit and count the days since the variance.

    Hello,

    In column A I have dates, in column B I have variance amounts for those dates.

    I want to find the most current variance from the current date that is over a certain amount and count how many days it has been since that date.

    For example, the variance to locate would be greater than $50 or less than ($50)

    Then it would calculate how many days have passed since the last variance of $50.

    I have a attachment that lays out what I am trying to do.

    Thanks for any help,

    Nick
    Attached Files Attached Files
    Last edited by avidcat; 06-17-2013 at 01:54 PM.

  2. #2
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Find the date of a variance amount over a limit and count the days since the variance.

    Perhaps something like this?
    Attached Files Attached Files
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    20,763

    Re: Find the date of a variance amount over a limit and count the days since the variance.

    You could try this array* formula in E2:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    *Confirm using CTR-Shift-Enter, rather than the usual Enter.

    Hope this helps.

    Pete
    Last edited by Pete_UK; 06-17-2013 at 12:11 PM.

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    20,763

    Re: Find the date of a variance amount over a limit and count the days since the variance.

    bump - not registered

  5. #5
    Forum Contributor
    Join Date
    12-15-2005
    MS-Off Ver
    Office 2007
    Posts
    346

    Re: Find the date of a variance amount over a limit and count the days since the variance.

    Thanks for both, I am testing out now.

    Nick

  6. #6
    Forum Contributor
    Join Date
    12-15-2005
    MS-Off Ver
    Office 2007
    Posts
    346

    Re: Find the date of a variance amount over a limit and count the days since the variance.

    Pete, I can only seem to get your formula to work with negative numbers, not if the total is more than $50 positive.

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    20,763

    Re: Find the date of a variance amount over a limit and count the days since the variance.

    Here's the file that I tested it on:

    Hope this helps.

    Pete
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    12-15-2005
    MS-Off Ver
    Office 2007
    Posts
    346

    Re: Find the date of a variance amount over a limit and count the days since the variance.

    Fotis1991, I am testing out the attachment, but I get a #VALUE! error when I change a amount, for example if I change B12 to $55. E2 displays a value error.

  9. #9
    Forum Contributor
    Join Date
    12-15-2005
    MS-Off Ver
    Office 2007
    Posts
    346

    Re: Find the date of a variance amount over a limit and count the days since the variance.

    Pete, thanks. I opened your attachement and see that it works. I did see one issue, the number of days is not correct. If the $50 variance is yesterday, it shows 3 days since last variance. But it should show 1 day.

  10. #10
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    20,763

    Re: Find the date of a variance amount over a limit and count the days since the variance.

    Ah yes, that's because your data starts on row 3 rather than row 1. Amend the formula as shown in red below:

    =IFERROR(TODAY()-INDEX(A3:A119,MIN(MIN(IF(B3:B119>=50,ROW(B3:B119))),MIN(IF(B3:B119<=-50,ROW(B3:B119))))-2),"")

    and then hold down Ctrl and Shift and press Enter, then release Ctrl and Shift.

    Hope this helps.

    Pete

  11. #11
    Forum Contributor
    Join Date
    12-15-2005
    MS-Off Ver
    Office 2007
    Posts
    346

    Re: Find the date of a variance amount over a limit and count the days since the variance.

    Perfect, thanks for the help Pete!

    Nick

  12. #12
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Find the date of a variance amount over a limit and count the days since the variance.

    Even that you found your solution i give my (slight modificated) suggestion, because:

    a) Maybe you'll need to use-specially if your data are too many- a non ARRAY formula solution...

    b) To see how my suggestion-really- works.
    Attached Files Attached Files

  13. #13
    Forum Contributor
    Join Date
    12-15-2005
    MS-Off Ver
    Office 2007
    Posts
    346

    Re: Find the date of a variance amount over a limit and count the days since the variance.

    Fotis, thanks for that update, I am testing it out now.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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