Splitting data in a bubble scatter chart

I have a table of data and a bubble chart of the data for mass distribution. x position to X, y position to Y and mass to bubble size.
The table has a field for type code (electrical, fittings, structure, etc).
I want to colour the bubbles based on type code. initially I thought of using a pivot table but bubble charts aren't available in pivots, so I tried making a series for each type and put a formula in the bubble size

Formula:

Please Login or Register  to view this content.

to make the series return 0 sized bubbles for items not in the type but it won't accept the formula.

Any ideas on how to do this without adding helper columns for each type code?

If you want to avoid helper columns, why not use names (Ctrl+F3) (but not with ranges but with formulas)?
Then names could be used in series
And as a formula I'd rather use IF(...="Elec",mass,"")
Hope it's clear. If not - use a hint from the yellow bar above.

Ok so technically that has worked but it lags the document out like mad.
It also won't let me input the named range in the series formula bar. Only in the api weirdly.

I'm not surprised that a SUMPRODUCT() type of approach would lag the document -- that approach would introduce a lot of duplicated effort.

My first thought for "conditional formatting" of a chart data series is this approach: https://peltiertech.com/conditional-...-excel-charts/ Obviously for a bubble chart you need two helper columns for each data series rather than one. Otherwise, I would expect it to be straightforward to program in the spreadsheet and to calculate/update very quickly.

I hope @Leon V (AW) have followed my suggestion and used IF not SUMPRODUCT with defined names.
If not, then indeed SUMPRODUCT could lead to slowing down the workbook

I have used IF to set the named range.

Formula:

Please Login or Register  to view this content.

It is still pretty damn crunchy. It tends to just freeze up for a few seconds every minute or so (but with no calculating in notification bar. I assume dynamic named ranges don't push that message.

Too bad. It will probably not improve even if you give up and do the same calculation in helper columns :-(
Is the dataset so large? Or may be some other formulas slow down the workbook?

It's a full bill of materials so the dataset isn't small but it isn't huge either.
depending on configuration it's between 15000 and 25000 parts.
there are other formulas on the doc but they are all just in line formulas volume*density, CG*mass, that sort of stuff and then the table totals row sums.
Do you think changing it from a workbook named range to a worksheet named range would have any effect?