percentage average (and nested formula)

Hi there,

my question is about the average of percentages (B6 up untill B30).
It’s about cell B2. There I want the average of 100%+100%+0% = 67%.
But it turns up with 92%.
Probably this has to do with FALSE statements in f.e. cell B2).
Unfortunately I don’t know how to solve them (the formula is not elegant but is works.)

I want in the spreadsheet a document "PvO" < 14 days under column F. It’s all about that is the document is signed within two weeks that is good. Otherwise it is wrong.

Statement:
1. in column F ‘PvO signed Y/N’ is only “N”. If the document is signed the project goes to another tab. So I don’t work with dates here.
2. In column E ‘Date PvO send‘ it says ‘nothing/empty’ or a date, or a text.

Statement:
A: date PvO send(date)
B: PvO signed (N)

If B-A< 14 -> 100% (green; good)
If B-A > 14 -> 0% (red; wrong)
If B=N -> 0% (white)


I already tried this for the averages:
=SUMIFS(B6:B30;B6:B30;0%;B6:B30;100%) turns up with 0%.
=(COUNTIF(B6:B30;"0%")+COUNTIF(B6:B30;"100%"))/COUNT(B6:B30) turns up with 100%
=((COUNTA(B6:B30)+COUNTBLANK(B6:B30))-COUNTBLANK(B6:B30))/(COUNTA(B6:B30)+COUNTBLANK(B6:B30))
and SUM(range)/COUNT(range), AVERAGE and SUM don’t work either.

HELP.

regards,
Mariëlle

hi Glenn,

that looks really well. Thanks!

Mariëlle

Hi,

I just added some good things to you're reputation , now looking for the button to close the thread,

kind regards,
Mariëlle