Formula Reduction

Hi all.

Excel Forum Question 1.xlsm

The attached calculator uses a number of cells in order to arrive at a final calculation. This currently works as intended. However, when I attempt to comine these formulas into one, I end up with more than 8195 characters maximum. Using the excel binary save this works, but I would prefer not to use that, favouring xlsm, or xls format.

It is absolutely required this formula is used by one cell only. The reason is becuase there are one to many tasks >subtasks and I need to create many tasks each with subtasks along a single row.

If this cannot be reduced further, is there a way to reference a formula that can be shared by mulitple cells in order to provide a different result: e.g. like a VBA function. (No, I don't want to use VBA).

A lot of time and contributions from various people around this forum have contributed to this attached task calculator, for which I thank all concerned sincerely.

I would appreciate any advice in order to try and reduce this formula

Cheers

Excel Forum Question 1.xlsm

Using multiple cells for a calculation is NOT a bad thing.
In fact, some times it's a good thing.
It's easier to troubleshoot your formulas if they are broken into pieces, rather than 1 long formula.

That's why XL provides you with 16,384 columns.
Is that really not enough columns do accomplish all your calculations?
You can actually hide all the columns that contain intermidiate calculations. and show only the resulting formula column.

Thanks for your response. While you can simply hide columns, I'm hoping for a more elegant solution.

How bout

Excel Forum Question 1 updated.xlsm

Thanks again for your suggestion. It's still basically hiding it, but perhaps I will just have to do it that way. Maybe it might actually be of benefit doing this way in order to produce reports later on.

Yes, for 2 big reasons.

1. Easier to troubleshoot issues when the calculations are broken into pieces.
2. The intermidiate calucations are often usefull in other ways anyway.

You may decide later that you also want to see the Day Count...
Oh snap, I used to have that in column P before I went and combined it all into 1 formula.
So now I'm going to make that calculation again, duplicating work that excel has already previously done.