Formulae for Doc Progress

  • Thread starter Thread starter Andos
  • Start date Start date
A

Andos

Hi ALL,

I was given the below formula by a fellow contributer,
but I am still finding that it's not doing what I need!
Can anyone help!

I am charting the submission cycle of a document and wish
to develop a formula to calculate a % completeness. So,
when a document is submitted 1st time to the Client, M11
cell gets a date input, and notes a 30% completeness
percentage. When the Client responds with an AIP/RSR or
NAP/RSR status the percentage will increase by 5% to 35%.
2nd submission issued to the Client (with date input into
cell U11) will give 55%. A response as AIP/RSR or NAP/RSR
a further 5% to 60%, etc. This could go on for 5
submissions.

However, if the submision gets an Approval (AP) status
then it is complete and will automatically get 100%,
irrespective which submission response!

The problem I have with the formula is that it always
gives 100%, no matter what I input into the given cells!

=IF(ISNUMBER(MATCH("AP",M11:AZ11)),100%,CHOOSE(COUNTA
(M11,T11,U11,AB11,AC11,AJ11,AK11,AR11,AS11,AZ11),30%,35%,5
5%,60%,75%,80%,95%,95%,95%,95%))

Thanks,

Steve
 
Steve,

I gave you this formula the other day and missed your reply. I just went
back and saw that you did have trouble with it, though I'm not sure why as
it worked exactly as you indicated it should when I tested it. My guess is
that I did not set up my test the same way you have your spreadsheet
arranged.

=IF(ISNA(MATCH("A",B1:B5))=TRUE,CHOOSE(COUNT(A1:A5),0.35,0.5,0.65,0.8,0.95)+
COUNTIF(B1:B5,"<>A")*0.05,1)

If you could describe how your spreadsheet layout I'm sure we can get this
to work. If you wish, you can e-mail an example and I can work with that.

PC
 
Back
Top