URGENT (at work) and CLUELESS.

**TilTandWiN** · 07-19-2007, 06:38 AM

I am trying to produce a bar chart that shows the distribution of monthly returns. This is basically a graph that shows the frequency of the number of consecutive months where a positive ROI (return on investment) was achieved and the frequency of the number of consecutive months where a negative ROI was achieved. (i.e. the labels on my x-axis read "0 to 2 months", "2 to 4 months", "4 to 6 months", etc... and the y-axis shows the frequency of these periods.)

The problem is that I only have the raw data (i.e. the actual ROIs) and was wondering if it is possible to create a formula that allows me to calculate a frequency of consecutive negative or positive periods.

If this is possible could you please try and explain it to me.

If you do not understand what I have written just post what you don’t understand and I will try to explain it.

Thanks

**TilTandWiN** · 07-19-2007, 06:39 AM

I am trying to produce a bar chart that shows the distribution of monthly returns. This is basically a graph that shows the frequency of the number of consecutive months where a positive ROI (return on investment) was achieved and the frequency of the number of consecutive months where a negative ROI was achieved. (i.e. the labels on my x-axis read "0 to 2 months", "2 to 4 months", "4 to 6 months", etc... and the y-axis shows the frequency of these periods.)

The problem is that I only have the raw data (i.e. the actual ROIs) and was wondering if it is possible to create a formula that allows me to calculate a frequency of consecutive negative or positive periods.

If this is possible could you please try and explain it to me.

If you do not understand what I have written just post what you don’t understand and I will try to explain it.

Thanks

**Dav** · 07-19-2007, 07:16 AM

maybe some thing like the attached is a start

create a column of 0s or 1s depending on if the value is positive, i have taken 0 roi to not be positve and hence return a 0
if you then do an if statement in the next column say if the value is not 0 then add it to the value above. This gives you the value of consequetive increases

then in the table these values are added up. The problem is that a run of 5 conseqetive months will also produce a count of a 4, a 3, a 2, and a 1 and these then have to be subtracted hence the last column in the table

Regards

Dav