Rolling Percentage issue

[mattbird]

Hi,

I am trying to input a formula to provide a rolling percentage, but can't seem to figure it out:

I have 12 cells D4:O4 and in the cell I have a number or letter ie:

1= attended
0= did not attend
A= apologies
D= stand in

in cell T4 i would like it to return a percentage. If I place a 1 or 'D' in D4 I want it to return 100%, if I then place a 1 or 'D' in E4 I want it to return 100% and so on all the way to O4. However, if I place a 0 or 'A' in any of the boxes lets say in F4 it will work out the percentage.

Hope this makes sense

Thanks Matt

 

[Fluff]

How about

=SUM(COUNTIFS(D4:O4,{1,"D"}))/COUNTIFS(D4:O4,"<>")

 

[mattbird]

How about

=SUM(COUNTIFS(D4:O4,{1,"D"}))/COUNTIFS(D4:O4,"<>")

Thanks for the help

The formula sort of works. Adding 1 or D to the cells returns 100%, but if I add 0 or A it returns the over all percentage which is not what I need.

if 11 out of the 12 cells have 1 or D the percentage would return 92% (rounder up with no decimal places), if D4 and E4 has 1 or D it would return 100% if the next cell F4 had a 0 or A it would return 92% and this would remain a 92% until another cell had 0 or A entered in it.

Also if there is nothing in any of the cells I need it to show 0%.

Thanks

 

[Fluff]

Maybe

=IF(COUNTIFS(D4:O4,"")=12,0,SUM(COUNTIFS(D4:O4,{1,"D",""}))/12)

 

[mattbird]

Thanks for the help

The formula sort of works. Adding 1 or D to the cells returns 100%, but if I add 0 or A it returns the over all percentage which is not what I need.

if 11 out of the 12 cells have 1 or D the percentage would return 92% (rounder up with no decimal places), if D4 and E4 has 1 or D it would return 100% if the next cell F4 had a 0 or A it would return 92% and this would remain a 92% until another cell had 0 or A entered in it.

Also if there is nothing in any of the cells I need it to show 0%.

Thanks

Trying to think of away to explain this better.....
If I place a 0 or A in the first cell D4 it returns 0%, but I need it to return of example 92%. if in E4 I do the same it will return 83% and so on down to 0%. however, if I placed 1 or D in the next cell F4 it would remain at 83%.

Example 1
D4 E4 F4 G4 H4 I4 J4 K4 L4 M4 N4 O4 T4
1 1 1 1 D = 100%

or

D4 E4 F4 G4 H4 I4 J4 K4 L4 M4 N4 O4
1 1 1 1 D D 1 1 1 D 1 1 = 100%

Example 2
D4 E4 F4 G4 H4 I4 J4 K4 L4 M4 N4 O4
0 A 1 1 D = 83%

or

D4 E4 F4 G4 H4 I4 J4 K4 L4 M4 N4 O4
1 1 1 1 D 0 1 1 A 1 D 1 = 83%

Does this make sense?

 

[Fluff]

That's what the formula in post#4 does.

 

[mattbird]

Maybe

=IF(COUNTIFS(D4:O4,"")=12,0,SUM(COUNTIFS(D4:O4,{1,"D",""}))/12)

That's what the formula in post#4 does.

Yes just seen, thanks you reply just before I sent my message.

 

[mattbird]

Maybe

=IF(COUNTIFS(D4:O4,"")=12,0,SUM(COUNTIFS(D4:O4,{1,"D",""}))/12)

Thanks this does what I was trying to explain

 

[Fluff]

You're welcome & thanks for the feedback.