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

**avidcat** · 06-17-2013, 11:41 AM

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

**Fotis1991** · 06-17-2013, 11:57 AM

Perhaps something like this?

**Pete_UK** · 06-17-2013, 12:09 PM

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

**Pete_UK** · 06-17-2013, 12:12 PM

bump - not registered

**avidcat** · 06-17-2013, 12:28 PM

Thanks for both, I am testing out now.

Nick

**avidcat** · 06-17-2013, 12:32 PM

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

**Pete_UK** · 06-17-2013, 12:35 PM

Here's the file that I tested it on:

Hope this helps.

Pete

**avidcat** · 06-17-2013, 12:36 PM

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.

**avidcat** · 06-17-2013, 01:22 PM

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.

**Pete_UK** · 06-17-2013, 01:32 PM

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

**avidcat** · 06-17-2013, 01:54 PM

Perfect, thanks for the help Pete!

Nick

**Fotis1991** · 06-18-2013, 01:51 AM

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.

**avidcat** · 06-21-2013, 12:00 PM

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

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

LinkBack

Thread Tools

Rate This Thread

Display

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

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

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

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

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions