Excel help

HI guys,

Dont use excel that much but I wondered if someone can help me.

I got two columns of data.

One is a benchmark return column, one is my returns for the same period next to it.

They are positive and negative percentages running all the way down.

I want my columns to just show all the times the benchmark wasa negative and what my performance was that same time to compare.

What is the easiest way to do this?

I would think the easiest way to accomplish this is to add two new columns and use something like =IF(A1<0,A1,"") for the benchmark and =IF(A1<0,B1) for your returns. Note that A1 has the benchmark an B1 has your return.

You could also use conditional formatting and make uneeded data hidden I’d do what Chad suggested. Then I’d rank the nonblank returns. I’d then max that array to get the largest count. I’d have a list that starts with that max and counts down to 1. Then I’d use index match to transpose the data into list that generates with no blanks between returns and updates as the values change

I was told that any periods where the benchmark was negative, I had to compare our returns and for positive returns just mark them as 0.

Chad, I have done your suggestion with A1 being benchmarks and B1 being my returns

IF(A1<0, B1,0).

This has put any numbers that are positive in the benchmark with a 0 next to it, and the remaining negative benchmark numbers just show my number next to it and not a 0.

I think this is easiest but please let me know otherwise.

THanks for your help Chad and Rawraw

Is there a way to add a spreadsheet? I wrote a macro for you that should do the trick.

click data tab. click filter button. you’ll have an arrow at the bottom right of the two top cells. click the arrow. click ‘number filters’. click ‘greater than or equal to’

do that in the benchmark column and it will display your results the way you want. simplest way if you just want to visually compare

A quick and dirty way to do this is just to sort the columns with the benchmark returns in ascending order. Then chop off all data with a benchmark return of 0 or greater. If you then want to restore the chonology, go ahead and resort the columns in chronological order.

The data tab filter is probably a better technique for an ongoing project. But if you are just doing the analysis once or twice, my way can save you some time on the learning curve.

We are trying to teach this man to fish and you are inviting him to an all you can eat Chinese buffet!!! (just kidding, the less people know about Excel the more relative value I contribute hehe)

Speaking of chinese buffets…I have never had an asian woman…half asian yes…but not full asian…is it worth it?

Sounds like the OP is being asked to compute downside deviation or semivariance. If so, remember you ought to be doing this with excess returns.

Chad’s way also works well, and doesn’t involve chopping off excess data. The only issue is that sometimes statistical functions do odd things with empty cells. Usually they just ignore them, but every once in a while they do someing odd like choke on the calculation or count the spaces in N. I tend to avoid those approaches when I can, just because I don’t fully trust the answers. I suspect excel behaves better these days than it used to, so it’s probably not a real problem anymore.

If you generate a new list like my slightly too complex answer above, you can then embedde offset into any function to only use cells with data in them.

Thanks guys, all very useful stuff. Just went for an If formula in the end as I had to show positive months with a 0 value. So set positive numbers to false = 0.

Seems odd to use zero for positive bmk months. That would throw off any stats you try to calculate from the series, although if you are just trying to produce a graphic, perhaps it’s not so bad.

Though perhaps there’s an embedded put option and 0 actually is the payoff for up periods in the benchmark.


I was doing some rolling return analysis of the funds performance vs a benchmark. I wanted to look at whenever the rolling index was down and compare it to our rolling in the same period. Setting a binary 0 for any positive months. Then gonna take a correlation between them. I’m only conerned at the moment with negative comparisons but didnt want to remove out all the positive stuff. will then look to do a regression analysis etc.

But most importantly, my boss wanted me to do it that way, so couldn’t argue haha.

Didn’t make that clear. It was not to replace the positive months with a 0. A column to the right with 0’s going down the positive months.

Take the correlation with the history that includes the zeros and a correlation of the history with the positive benchmark periods removed from your sample. I think you will find them different, most likely by more than a little. They will be the same only for cases where there is zero correlation between your history and the benchmark during up months (unlikely)

If you are trying to establish a correlation between your returns and the benchmark returns for periods when the benchmark is down, the one with the up periods removed from your sample is the one that is correct.

How different they are depends on a lot of things, but setting one or both of your variables to zero instead of missing is definitely going to bias your results.

Cheers Bchad :slight_smile: