How to force PERCENTOF to spill results

by

The new PERCENTOF function in Excel is much welcomed; however, I’m trying to overcome one of its limitations. I don’t want to use multiple formulas to achieve a set of results, but I haven’t found an easy way to spill them with a single one.

enter image description here

I have tried =PERCENTOF(INDEX(A2:A6,SEQUENCE(5)),A2:A6), hoping it spills each percentage separately, but the data_subset argument doesn’t support it. It only does the same as =PERCENTOF(A2:A6,A2:A6) by returning 100%.

Is there a way?

  • 2

    Didn’t hear about this new function yet, but you may try: =MAP(A2:A6,LAMBDA(z,PERCENTOF(z,A2:A6)))

    – 




  • 1

    The map formula works for me. I guess the reason the original formula doesn’t spill is that the first argument of the call to percentof is generally an array.

    – 




  • 1

    @user11222393 Yes, it does work now. It was because before you edited your formula, the one I copied and pasted had z in an extra argument unnecessarily.

    – 




  • 1

    @Statto sorry for that, it was a matter of seconds till I realized mistake and didn’t think you have already copied that!

    – 

  • 2

    =MAP(A2:A6,LAMBDA(a,PERCENTOF(a,A2:A6))) works, as well as =GROUPBY(A2:A6,A2:A6,PERCENTOF) which includes the array itself followed by the individual PERCENTOF results. I tried leaving out totals =GROUPBY(A2:A6,A2:A6,PERCENTOF,,0) but that makes my Excel crash, but it’s still Beta phase.

    – 

Leave a Comment