A formula needed to calculate my work when it is 100% complete.

**Vic78** · 10-18-2010, 07:50 AM

Hi,

I am currently working on a list project in Excel 2003 that requires an updating formula to show a percentage in my progress when a task is completed with a "Y" for each row (up to 100%). I have already made a formula to count the Y if the task is complete:

I put this formula in cell F2509

=COUNTIF(F3:F2501,"Y")

and a formula in cell F2511 to count the 'blank' cells - as in 'to be completed'

=COUNTBLANK(F3:F2501)

There are 2499 (F3:F2501) items in the list that would need the letter Y once that job has been approved. I am trying to make the formula in cell F2513 count the number of completed Y cells in colum "F" which provides myself with constant % update with my performence.

I have tried to divide cells F2509/F2511 but this just leaves me with the error #DIV/0 as cells F2511 = 0 and F2509 = 2499 vice versa when all the work has been amended with a Y (as in all my work is complete 100%) to show cell F2511 = 2499 and F2509 = 0.

I have also tried the formula below which does state 100% when the work is finished but it turns to a minus % when I try and delete the Y, which means this is not accurate:

=(F2509-F2511)/ABS(F2509)

All I want is a progressive formula to get to 100% when my job is done!

I hope this makes sence as this the first time I have used a forum.

Cheers for any help!

**Marcol** · 10-18-2010, 08:14 AM

Hi Vic78 Welcome to the Forum

This any good?

=IF(COUNTIF(F3:F2501,"Y")=2499,"Done",2499-COUNTIF(F3:F2501,"Y")&" Tasks to do.")

Hope this helps

**DonkeyOte** · 10-18-2010, 08:16 AM

Wouldn't the % complete calculation be:

Please Login or Register  to view this content.

**Cutter** · 10-18-2010, 08:21 AM

I think you want:

Please Login or Register  to view this content.

and format as %

**Marcol** · 10-18-2010, 08:29 AM

Sorry about missing the percentage bit.

DonkeyOtes' solution is the way to go.

However this might still of some use

=IF(COUNTIF(F3:F2501,"Y")=ROWS(F3:F2501),"Done",ROWS(F3:F2501)-COUNTIF(F3:F2501,"Y")&" Tasks to do.")