I'm trying to correct this formula because it's not returning the right value.
=INDEX(EBITDA_Forecasted!$FP$5:$IU$5,MATCH(TRUE,EBITDA_Forecasted!$FP7:$IU7>0,))
What I'm trying to do is index row 5 (dates going across) and looking for row 7 (when treatments are >0) and returning the date value.
Did I mess up the formula somewhere?
Last edited by holycowbanana85; 07-24-2012 at 04:49 PM.
Try
=INDEX(EBITDA_Forecasted!$FP$5:$IU$5,MATCH(TRUE,EBITDA_Forecasted!$FP7:$IU7>0,0))
Try it and it didn't work. It is returning the end of the first month, it should be like several months down the row.
---------- Post added at 02:03 PM ---------- Previous post was at 01:55 PM ----------
What's funny, I used this formula and it worked for another table I was creating.
=INDEX(EBITDA_Forecasted!$B$5:$CG$5,MATCH(TRUE,EBITDA_Forecasted!$B7:$CG7<0,0))
Instead of <0, I'm making it >0. Somehow it's not working. Am I missing something?
Are you entering it as an array? Try
=INDEX(EBITDA_Forecasted!$FP$5:$IU$5,MATCH(1,--(EBITDA_Forecasted!$FP7:$IU7>0),0))
---------- Post added at 03:04 PM ---------- Previous post was at 03:03 PM ----------
What sort of values do you have in EBITDA_Forecasted!$FP7:$IU7 ?
In FP7 and IU7, it's positive integer numbers. The previous formula is not working as well =(.
The value it's returning is 1/31/2006 and not 1/30/2010. Not sure why it's not working since it's as simple as changing a sign from "<" to ">".
If you're going across the row, then try:
as CSE.
Please Login or Register to view this content.
Would you like to say thanks? Please click the: "Add Reputation" button, on the grey bar below the post.
still not able to work. There are values of zero in row 7, does that affect anything? The logic seems to be making sense, (looking for the first value in row 7 that is greater than zero, and returning the date in row 5).
You are entering the formula with Ctrl-Shift-Enter, right?
Yes I am. Not sure why it's not working thou?
Post a workbook stripped to the essentials that demonstrates the problem.
Entia non sunt multiplicanda sine necessitate
When I revert back to this old formula of "<" 0,
=INDEX(EBITDA_Forecasted!$B$5:$CG$5,MATCH(TRUE,EBITDA_Forecasted!$B7:$CG7<0,0))
It spits out the correct date.
When I change the sign and look at a different table, it does not work. I'm extremely baffled.
If you guys look at Analysis tab, cell C3, this is where I'm entering the formula. I tried toying around ">"0 to 100 but it's returning the wrong date. It should be 1/30/2010 for row 7. Thanks for the help so far!
In EBITDA_Forecasted!$B7:$CG7 all your zeros are entered as text (with single apostrophes in front).
Does the apostrophe matter because the first time I did it, it worked with the apostrophes?
Yes, the apostrophes matter. In Excel a test string (even a text zero) is larger than a number (or a date value).
The formula works when the apostrophes are removed.
Thanks, I will try to troubleshoot. Will keep you guys updated on this. =)
---------- Post added at 03:46 PM ---------- Previous post was at 03:30 PM ----------
THANK GOODNESS it works!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks