Formula for dividing 2 columns then multiply by 12

Alisia Gill

I am trying to get the average for a specific category (Column 1), by dividing YTD Terms (Column 2) by YTD Headcount (Column 3) then multiply by 12 for my Column 1?

Here is the formula I am trying and I get Unparseable.

=COUNTIFS({Turnover ratio by Dept July 2020_YTD Term}, /{Turnover ratio by Dept July 2020_Average Employees}12*, {Turnover ratio by Dept July 2020_Department}, "Provider")

Paul Newcome

4 divided by 24.5 is 0.163 which when multiplied by 12 comes out to 1.956 so it is calculating properly. To move the decimal one position to the right you will need to multiply the final result by 10.

=(([YTD Terms]1 / [YTD Headcount]1) * 12) * 10

Paul Newcome

You would need to do something aliong the lines of...

=(COUNTIFS(category formula) / SUMIFS(headcount formula)) * 12

Alisia Gill

I got an incorrect Argument Set

=(COUNTIFS({Turnover ratio by Dept July 2020_YTD Term}) / SUMIFS({Turnover ratio by Dept July 2020_Average Employees}, {Turnover ratio by Dept July 2020_Department}, "Provider")) * 12

Now, I do have a formula on my worksheet to count the YTD Terms and that total is 4, and a formula giving the SUMIF of the YTD Headcount , total is 24.50 already. I tried to do this formula below and the answer it gave me was 12 and that is incorrect. The answer should be 20.99%

=COUNT([YTD Terms]1 / [YTD Headcount]1) * 12

Alisia Gill

Paul Newcome

Ok. So the issue with your first one is that you are not building the functions properly. You should have a full COUNTIFS divided by a full SUMIFS.

=(COUNTIFS({1st range}, 1st criteria, {2nd range}, 2nd criteria, ........) / SUMIFS({sum range}, {1st range}, 1st criteria, {2nd range}, 2nd criteria, ..............)) * 12

If you already have the numbers on your sheet, then you should be able to use:

=([YTD Terms]1 / [YTD Headcount]1) * 12

Alisia Gill

I can use the second formula however, the decimal isn't placing correctly. It should read as 20.99 not 1.96. If I move the decimal using the menu bar action for decimal moving I get 2. If I use the percent tab, I then get 196%

Paul Newcome

4 divided by 24.5 is 0.163 which when multiplied by 12 comes out to 1.956 so it is calculating properly. To move the decimal one position to the right you will need to multiply the final result by 10.

=(([YTD Terms]1 / [YTD Headcount]1) * 12) * 10

Alisia Gill

I believe this worked, they are checking the numbers. Thank you for your help.

Paul Newcome

Happy to help. 👍️