Formula to calculate percentage excluding certain value

Chrisj1123

I have a sheet that we use to track travel. I have a formula that calculates the percentage each person is traveling per quarter but also how much of our team is traveling per week. We have 14 people which is what the current formula is based off of. I need to modify this formula to exclude when someone is on vacation "VAC" so it is not calculated in the total travel formulas.

Example of my current formula for each week is:

=(14 - COUNTIF([17-Jun]2:[17-Jun]15, "")) / 14

KPH

Got it.

So if your original formula is counting the number of things in the column and dividing that by either 14 or the number of names like this

=COUNT([12-Feb]1:[12-Feb]14) / 14

or

=COUNT([12-Feb]1:[12-Feb]14) / COUNT($LPM1:$LPM14)

Then your revised formula will have a COUNTIF at the start to only count the rows that are not equal to VAC

=COUNTIF([12-Feb]1:[12-Feb]14, "<>VAC") / 14

or

=COUNTIF([12-Feb]1:[12-Feb]14, "<>VAC") / COUNT($LPM1:$LPM14)

KPH

It looks like you have one person per row and are working out the percentage travel by dividing the number of people with data in your 17-Jun column by the number in the team.

How do you record when someone is on vacation?

And how do you want the calculation to work? Will you remove the vacationer from the denominator, so if you have 2 people traveling and 1 on vacation the percentage will be 2/13. Or do you just want to remove them from the numerator so their lack of data in the column isn't counted (so 2/14)?

Chrisj1123

So yes. There is one person per row and each column is a different week. Calculation for percentage is at the bottom of the column in its own row.

Basically I want the formula to work that it does not count the cell if it is marked VAC which is how we indicate someone on vacation. In the case of the picture below I would want 19-Feb and 26-Feb to show 0% vs the 7% it shows now. But still count if someone put any other information in the other cells. The way we indicate our travel is by which site we will be at so there could be a wide range of sites in each column but I would only want to not count VAC

KPH

And by not counting VAC do you mean just remove that person from the top part of the percentage calculation or the top and bottom?

For example ...

If 2 people have things other than VAC the calculation is 12/14 = 14%

If 2 people have things other than VAC and one has VAC do you want to just ignore the VAC from the top part i.e. still 12/14 = 14%

Or do you want to remove them from the bottom part as well, so the formula is 12/13 = 15%

Chrisj1123

I would just want to ignore the VAC from the top part of the equation.

So this example:

If 2 people have things other than VAC and one has VAC do you want to just ignore the VAC from the top part i.e. still 12/14 = 14%

KPH

Got it.

So if your original formula is counting the number of things in the column and dividing that by either 14 or the number of names like this

=COUNT([12-Feb]1:[12-Feb]14) / 14

or

=COUNT([12-Feb]1:[12-Feb]14) / COUNT($LPM1:$LPM14)

Then your revised formula will have a COUNTIF at the start to only count the rows that are not equal to VAC

=COUNTIF([12-Feb]1:[12-Feb]14, "<>VAC") / 14

or

=COUNTIF([12-Feb]1:[12-Feb]14, "<>VAC") / COUNT($LPM1:$LPM14)

Chrisj1123

Thats got it! Thank you!

KPH

Glad I could help.