> ... That formula works a treat as the file reduces by 30mb
That's good to hear, that it works for you ... and with the side benefits

> ... because the file has so many calculated cells it takes 10 minutes to do a calculation on an i5 processor
well, that's the side effect of using array formulas.
(a) You could try reducing the data ranges in the formula to the smallest extent just large enough to cover. The smaller the range, the faster the calcs.
(b) Consider using manual calc mode. Press F9 only when it is required to calc, eg on completion of all data entry (by batches) or setting of params (DVs for example). I do use this manual mode quite a fair bit, and it makes things manageable/tolerable

> ... Is there a way I can get my result by using VBA code as I read this is a much more efficient way of doing things?
I don't know. I do understand that vba runs slower than formulas. My 2 cents views
Do a posting in programming forum if you seek this route

---------------------
Any worth? Yes? Whack the little star at the bottom left of my responses