Excel Problem on Multiple Consecutive Negative Numbers - Challenging :(

Hi all,

I have been stuck on a problem for quite some time and I was hoping someone can lend me a hand.

Table.PNG

Basically, given a horizontal list of years and values, it would be helpful to have formulas that return the year where negative values happen, and also how long it lasts for.

I have tried using INDEX, MATCH, FREQUENCY (in which transpose might have to be used because frequency works on vertical lists).

However, I just can't come up with a solution no matter how I try. I have attached the file within.

Please point me in the right direction.

Thanks!

hi can you the logic why 2004 is not your required result

Punnam

Hi Punnam,

This is because the table should only return the instance where it dips into the negative values, and then how long it stays. So in the case above, it would dip into the negative values in 2003, and remain like that for two years, so 2003 & 2004. Similarly, we can see that the 5th time it dips into the negative value is in 2010, where it lasts for 3 years, so that includes 2010-2012.

Originally Posted by canapone

Hi

first answer: how to list years in C7

Please Login or Register  to view this content.

Hope it helps

Hi canapone,

I appreciate your prompt response and help on the first half, it works nicely!

Thank you so much! Not only do they work perfectly, but you answered very quickly too.

I really appreciate it!!!

Originally Posted by canapone

Hi again

in C7

Please Login or Register  to view this content.

Not sure if formula is robust.

Cheers

Hi,

I didn't mean to cross post.

What happened was that I posted in the other forum, but realized it has limited functionalities such as it does not support uploading an excel file so it can be more clear.

Then I searched if there is a better forum board and I did here. The utilities were much better and users were more active.

It was simply a change in preference of using this forum over another forum board. Now that I have moved here, it won't happen again

PS: I did try to edit & delete the post in the other forum, but there was no such option (another limitation).

Originally Posted by JBeaucaire

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).


CROSSPOST link added for you as a courtesy: http://www.mrexcel.com/forum/excel-q...allenging.html

Do not do this again. Thank you.