Calculating numbers together as percentage

[LuizDiaz]

Hello,

I have data


Col A

1
2
1
3
1
4
1
5
1
6
6
5
4
3
2

I need a formula that will give me a % of scores that are 2 and higher - for example above has 15 scores and 10 are 2+ so it is 66%

I need a formula that will count how many 1's and 2's and 3's etc there are in Col A and this will be kept in Col B (Number of 1's B1 / Number of 2's B2)

Thanks in advance

[AliGW]

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to consider amending your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Guideline #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

There is NO 2023 version of Excel - what do you have? Please update your profile NOW.

[AliGW]

In any blank cell:

=COUNTIF(A1:A15,">="&2)/COUNT(A1:A15)

Format the cell as PERCENTAGE.

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	A	B	C
1	1
2	2
3	1
4	3
5	1
6	4
7	1
8	5
9	1
10	6
11	6
12	5
13	4
14	3
15	2		67%

Sheet: Sheet1

[AliGW]

Thanks for improving the title - now please update your profile.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

[LuizDiaz]

Thank you, will amend to solved when it is solved but only half my problem has been solved so far. Thank you for your help, sorry for not being better at my first attempt ALIG

[AliGW]

Yes, but you have stubbornly refused to update your forum profile. When you have done that, I'll help you with the rest, which you added AFTER I had provided the original solution. Naughty!

It is AliGW, NOT AliG - please get it right.

[AliGW]

There is no Excel 2017 - please check properly! Is it 365 that you have?

[LuizDiaz]

I did not stubbornly refuse, I thought I had successfully done it

Pretty sure this part was always on the question but anyway 'need a formula that will count how many 1's and 2's and 3's etc there are in Col A and this will be kept in Col B (Number of 1's B1 / Number of 2's B2)'

[AliGW]

Now you say you have Excel 2021, so this should work. In E1:

=LET(s,SORT(UNIQUE(A1:A15)),c,COUNTIF(A1:A15,s),HSTACK(s,c))

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	E	F
1	1	5
2	2	2
3	3	2
4	4	2
5	5	2
6	6	2

Sheet: Sheet1

I did not stubbornly refuse, I thought I had successfully done it

OK - you chose to ignore my request until just now - how about that?

Bear in mind that I asked you to do this in an earlier thread and you ignored me then as well.

[LuizDiaz]

i amended to 365, oopsy sorry

[AliGW]

Oh, so we've had 2023, 2017, 2021 and now 365!!!

See post #9 for your solution. Please mark the thread as solved now.

[LuizDiaz]

I might have missed it, i dont read so good, would never be stubborn and not do as requested

[AliGW]

Good to know.

Did you see post #9?

Please mark the thread as solved.

[LuizDiaz]

I have a new problem please. I do not fully understand how to amend your above formula but I have another table


B C D E F
3 2 4 2
2 2 2 3 4
3 2 1 3
4 2 3 1

I now also need a table like this to count the uniquie number and how many times they appear

this is col 3/4/5/6 respectively

1 = x
2 = y
3 = z

last one i promise thank you thank you thank you forever

[AliGW]

No - this thread needs marking as SOLVED. Do this NOW.

Then open a NEW thread with a GOOD title AND a workbook attached.

Thanks.

[LuizDiaz]

Okay AliW will do

[AliGW]

It's AliGW.

Thanks.