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

1. ## 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

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

Perhaps something like this?

3. ## 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:
`Please Login or Register  to view this content.`

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

Hope this helps.

Pete

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

bump - not registered

5. ## 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. ## 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. ## 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

8. ## 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. ## 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. ## 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. ## 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. ## 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.

13. ## 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.

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

#### 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