how to find percentage conversions

[mansighai]

i have two columns (A & B, A containing year of receiving and B containing year of submitting. How can i calculate percent conversion of items received and items submitting (Items subkitting/items received*100)?

[Sam Capricci]

are you saying you want a percentage of those where the application year (col A) is equal to the grant year (col C)? Because this does not quite match what you wrote in your first post nor do any of the grant years submitted match the application years, so if that is your question, I'd say offhand the answer is 0%.
Perhaps you could rephrase the question or upload a possible answer and how you are arriving at it? Or is it supposed to be a count of those listed as Y in col B (granted) divided by the overall number which would be =COUNTIF(B2:B19,"Y")/COUNTA(B2:B19)

[mansighai]

Hi i am sorry for the confusion. Lets take excel file data as an example and I am trying to get year wise percentage. For e.g. output should be for 2017: 100% (4 total application and 4 granted); for 2018: 50% (4 total applications and 2 only granted); for 2019:50% (4 total applications and 2 granted)

[etaf]

i had the same issue when trying to work out what %

so you only need to see the application year

2018 - 4 applied for , BUT only 2 granted

=COUNTIFS(A2:A19,E1,B2:B19,"Y")/COUNTIF(A2:A19,E1)
where E1 you enter the year you want

or you have to hardcode
=COUNTIFS(A2:A19,"2018",B2:B19,"Y")/COUNTIF(A2:A19,"2018")

So it has nothing to do with the granted year - just the application column A - and if Y in column B

[avk]

Please Login or Register  to view this content.

In "D2"
copy paste down