Hello,
I have a two column list of "Days" and "Dollars." Days increase, dollars decrease. The amount the dollars decrease changes as you play around with some variables determined elsewhere. It's basically a quick forecasting tool, and allows you to fiddle with numbers to see when you'll get to a particular dollar amount.
Consequently, I would like a way to have a cell display the "Day" that closest matches a predetermined dollar amount defined in another cell. Rough is OK, it doesn't matter if it is slightly over or slightly under.
In more real world terms, suppose I have days 1-365 in column A, and dollar amounts starting at 5,000 and ending around 1,000 in column B descending. I'd like to be able to put "$3384" in a cell, and have another cell display the Day that is more or less closest to that amount.
Is this possible in real time? (ie, something that changes as you change numbers on the worksheet?)
Thanks,
Nathan
Last edited by Nathanf; 01-11-2012 at 05:46 AM.
Let's say you're entering your search $ value in cell M1.
In column C you could put a "key" formula to assist with this. In C1 put this formula:
=ABS($M$10-$A1)
Copy that down the whole data set.
Then in N1 you can put this formula to bring back the closest day matching your M1 value:
=IF(M1="", "", INDEX(A:A, MATCH(SMALL(C:C, 1), C:C, 0))
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Thanks! It took some tweaking and looking up what those particular functions do, but it's working perfectly now. Your expertise is much appreciated!
Supposed your dates are in A1:A365, amount in B1:B365, your seach amount in E2, return date in F2.
In F2:
=MATCH($E$2,$B$1:$B$365,-1)+IF($E$2>=AVERAGE(INDEX($B$1:$B$365,MATCH($E$2,$B$1:$B$365,-1)),INDEX($B$1:$B$365,MATCH($E$2,$B$1:$B$365,-1)+1)),0,1)
If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Thanks much!Originally Posted by bebo021999
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks