AVERAGEIFS excluding cells that contain specified text

1
votes

I am trying to calculate average leverage levels for years 2010-2017 with AVERAGEIFS, but would want to exclude Infrastructure sector.

Here's a formula that includes all sectors (simplified by removing the $-signs):

=AVERAGEIFS(V186:V891,J186:J891,H164)

V: leverage, J: year, H: year next to the formula cell

I tried doing this:

=AVERAGEIFS(V186:V891,J186:J891,H165,K186:K891,{"Communication","Consumer Discretionary","Consumer Staples","Energy","Financials","Healthcare","Industrials","Materials","Other","Technology","Utilities"})

K: sectors

My try to include all other sectors than Infrastructure doesn't work for some reason, because the average I get for e.g. year 2012 is 3.5 with the 1st formula, and 3.1 with the 2nd, although there're no Infrastructure deals that year. Additionally, some years return #DIV/0!.

I also tried to search for a way to write the formula that excludes Infra deals in a simpler way, but didn't find anything. I'm thinking something like this:

=AVERAGEIFS(V186:V891,J186:J891,H164,K186:K891,NOT("Infrastructure"))

Question summarized: how could I calculate the average leverage level for each year, excluding infra deals?

2
excelexcel-formula

2 Answers

2
votes

Your formula is almost there with this one:

=AVERAGEIFS(V186:V891,J186:J891,H164,K186:K891,NOT("Infrastructure"))

I haven't tested this, but have a go with it, it should exclude Infrastructure.

=AVERAGEIFS(V186:V891,J186:J891,H164,K186:K891,"<>"&"Infrastructure"))

<> is often used in Excel formulas as it means 'not equals'.

Thanks, Dom

1
votes

The syntax is just:

=AVERAGEIFS(V186:V891,J186:J891,H164,K186:K891,"<>Infrastructure")