automatically updating formula when user adds a user

[Muzza86]

I have a spread sheet which I have entered a formula to calculate the percentage of users in the list that have completed a certain training.

this works and I KNOW how to update it if a new employee enters company and needs to be added to the list, BUT the person I am creating this spread sheet for DOES NOT.

what I need is, a formula or vba to allow me to tell excel to change the formula automatically so the end user doesn't have to.

e.g. at the moment there are 54 employee's so I use a percentage formula like... 5/54*100 = 9% (to 1dp). which shows that 5 employees out of the CURRENT total have been trained.

What I need is the formula to automatically update when my end user has to add to a couple of rows which makes the total employees 56. hence I need the formula to change to /56*100 to maintain the correct percentage..


can this be done??

I have attached simple example sheet

[Fotis1991]

Please Login or Register  to view this content.

[TMS]

One way:

Please Login or Register  to view this content.

and another:

Please Login or Register  to view this content.

Regards, TMS

[ragulduy]

Try:
=COUNTIF(G4:G1000,"yes")/COUNTA($F4:$F1000)
in K5 and
=COUNTIF(H4:H1000,"yes")/COUNTA($F4:$F1000)
in L5

[TMS]

You don't need to use the intermediate table. You can use:

Please Login or Register  to view this content.

or:

Please Login or Register  to view this content.

Regards, TMS

[Muzza86]

thanks guys ill try these out. appreciate it

[Muzza86]

went with ragulduy

thanks mate perfect

[Muzza86]

slight issue.. as I posted this as an EXAMPLE ..your answers were perfect... however I was ignorant.

in my real table.. the data in the cell is not "yes" and obviously this would be easy to change if it was another word.. but the data in the cells under the training column is a DATE ..and they are all different...

see attached - ive updated it

[ragulduy]

Try:
=COUNTIF(G4:G1000,">0")/COUNTA($F4:$F1000)

[Muzza86]

legend, thank you. works

[TMS]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.