Count 0 and negitive numbers since last positive number from right to left

[gogreenpower]

Hi Guys,

I have attached an example. Basically I had this set up to calculate the row and give the answer at the end. But now I cant see the results without scrolling, so I want to move the answer fields to the C column and add new data into the D column while keeping all the data in the formula.

I hopr you can understand what I'm trying to say.

Thanks in advance,


Green

[bebo021999]

How about in C2:
=MATCH(TRUE,INDEX($D2:$I2>0,),0)-1

[gogreenpower]

That works for all C cells when I remove the, $, but if the row contains all "0" i get a N/A error.

Edit: Also it doesn't recognise negitive numbers.

[XOR LX]

Hi,

Since your numbers are only ever -1, 0 or 1:

=MATCH(1,D2:I2,0)-1

Regards

[gogreenpower]

Sorry the numbers in the real one are percentages, ranging from -100.00% to 500.00%. I just used those numbers for simplicity. Is there a way to get the formula to expand when I add a new data column in column D pushing the older ones to the right?

Thanks

[XOR LX]

In that case could you re-post your workbook with some relevant data and indicate clearly your expected results?

Regards

[kvsrinivasamurthy]

Pl see attached file.

[gogreenpower]

When I entered new figures I get the #name? error. I have updated the workbook with real world data to help.

Pl see attached file.

[XOR LX]

Hi,

Why do you say the answer for row 3 should be 4?

Regards

[gogreenpower]

I have figured out that this is an Excel 2007 formula, can anyone rewite this to work with the 2003 version?

=_xlfn.IFERROR(SMALL(IF($D4:$I4>0,COLUMN($D4:$I4)-4,""),1),"")

[gogreenpower]

Sorry, I missed it, you are correct, it should be 3.

Hi,

Why do you say the answer for row 3 should be 4?

Regards

[XOR LX]

Try:

=MATCH(TRUE,INDEX(D2:I2>0,,),0)-1

Regards

Edit: Just realised that's exactly what Bebo gave way back in post #2!

[gogreenpower]

This works with every combination except if the row contains all 0 entries, it returns #N/A

Try:

=MATCH(TRUE,INDEX(D2:I2>0,,),0)-1

Regards

Edit: Just realised that's exactly what Bebo gave way back in post #2!

[XOR LX]

Well, what do you want it to return in those cases? There is no "last positive number".

Regards

[gogreenpower]

The total number of cells?. I have updated the workbook to show you. Also is there a way to get the formula to expand if I add a new data coloumn into 'Column D'?


Thanks for the help so far.

[Saarang84]

Hi Green,

For Excel 2003 version, use the below formulas :

Srinivas Murthy's solution :

Please Login or Register  to view this content.

You need to confirm that array formula is used by pressing CTRL + SHIFT + ENTER.

For Bebo / XOR LX solution :

Please Login or Register  to view this content.

[gogreenpower]

I have them both setup and working with the exception of this last question. Its in red on the workbooks, one for each formula incase one is easier than the other.

Thanks

[Saarang84]

Hi Green,

You already have the correct solution.. Its just a question of how it is being used.

You just need to insert a new column in between in your range (with %'s) after col D (or after the first column with %'s whatever), and need to paste the values of the first column with %'s into the newly created column and then overwrite the first column with new date & % values.

There is no need for any corrections with the below given solution.

[gogreenpower]

Hi Saarang,

Thanks for your help, your description is what I plan on doing, I just wondered if there was some type of dynamic formula that would save me the hassle. But that's ok, thank you for all your help. Much appreciated.

Regards,


Green