Formula needed to create a percentage where the goal is zero

[FFR495]

Hi Everyone,

I'm looking for assistance on creating a formula to show a percentage when the goal is zero. If the actual goes above zero than the percentage needs to go lower but if the actual stays at zero than the percentage needs to stay at 100%. Also this is over a 13 week period. (Picture reference below).

	Owner	Quarterly Goal	Quarterly Total	% of Qtly Goal	Weekly Goal	4/1/2022	3/25/2022	3/18/2022	3/11/2022	3/4/2022	2/25/2022	2/18/2022	2/11/2022	2/4/2022	1/28/2022	1/21/2022	1/14/2022	01/07/2022
# of Accounts Pending to be Posted		0	20	#DIV/0!	0	0	0	0	1	5	0	1	1	2	2	3	5	0

I hope this makes sense.

Thank you in advance!

Jenna

 

[6StringJazzer]

show a percentage when the goal is zero.

What as a percentage of what?

if the actual stays at zero

Actual what?

 

[FFR495]

The goal is to stay at 0 for the full 13 weeks, if they do so they would be at 100%. If the number goes higher than 0 the percentage needs to go down. I guess the actual would be 0.

 

[KenU]

I don't know if this is what you are after, but the formula below will give the percentage of weeks for which the goal (zero) was not met during the 13 week quarter. This formula assumes the provided data begins in cell A1 such that the formula itself is in E2 (format as %) and looking at the weekly data in G2:S2.

=1-SUMPRODUCT(--($G2:$S2>0))/13

Hope that helps.

Regards,
Ken

 

[FFR495]

Hi Ken,

It looks like this is going to work.

Thank you so much!!!

Jenna