Formula to capture one or multiple Values > 20%

[sherylt13]

Hello, looking for assistance on a formula that will capture one or more values that meet certain criteria, in this case those that are greater than 20%, so that I can then incorporate into a concatenate statement. In this example, I have a table that shows varied percentages, and in one category (called "High"), need to "write out" those that are above 20% in the High category.

Initially I was using a concatenate statement that included the REPT function, however it was not working 100%, so looking for added suggestions. The attached lists a sample with 10 'grades' and three of which are greater than 20% under the High category. This needs to be in a statement in preparation for a written analysis of the data.

In the attached, I need a concatenate statement to first state "The grade(s) above 20% <are/is> ".... and then have ability to select one or more above 20%, which are the 3, 8, and 9 grades.

As always, appreciate any assistance, thank you,
Sheryl

[Glenn Kennedy]

Your best way to achieve this is with a UDF.

Please Login or Register  to view this content.

How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)
[B][I]

Then:
="The grade"&IF(COUNTIF(D:D,">0.2")>1,"s","")&" above 20% in the High Category "&IF(COUNTIF(D:D,">0.2")>1,"are ","is ")&ConcatAll(IF(D3:D12>0.2,$B$3:$B$12,""),", ")

This is an array formula.

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[SjMaxwell]

I got pretty close without any code, but I had to add a helper in column G and didn't end up with an "and" between the numbers. Maybe someone can work out how to find the last comma and replace with "and"...
G3 Formula carried down =G2&IF(D3>0.2,B3&", ","")
Statement Formula ="The "&IF(COUNTIF(D3:D12,">20%")=1,"grade","grades")&" above 20% in the High category "&IF(COUNTIF(D3:D12,">20%")=1,"is","are")&" "&LEFT(G12,LEN(G12)-2)

[Glenn Kennedy]

I fiddled with a helper column, but couldn't get one to work. Yours does. Here's the last bit:

Please Login or Register  to view this content.

[SjMaxwell]

Awesome!
Now if we wrap it in an IFERROR it could work when no entries meet the criteria, output "There are no grades above 20% in the High category."

[Glenn Kennedy]

Indeed, we could. Since it's 99% your effort, I'll leave that to you....

[sherylt13]

Wow, thank you for the responses, will take a look through today and respond as soon as possible. Thanks!!

[sherylt13]

This works great! Thank you !!!!!

Sheryl