Simplifying a formula: Consolidate formula to calculate SLA

Can you upload example workbook?

Sure thing, will just finish transposing to other sheets in range to ensure no REF results and will upload :o)

Here you go - it wouldn't allow me to upload here as too large, so have uploaded to file hosting site:

Please Login or Register  to view this content.

I certanly won't register to get the file.
Please remove all formatting, extra data and leave only what's needed for example.

You need to get it less than 1MB.

Sorry! Didn't realise you would have to register to download; I have uploaded to another site -

Please Login or Register  to view this content.

The other formula in this one that needs to be shortened is this:

Please Login or Register  to view this content.

I have had to take out the first IF statement due to limitations, which is required to blank the cells so really need it in there to avoid user confusion (=IF(N('Thu (5)'!O25)=0,0,)

Don't have time to cut down at all tonight I'm afraid, it's past 9pm and I've been going since 7am, need some sleep before I get up at 6am tomorrow hehe :o)

I have now saved to XLSM format, which has brought the size down; please find attached.

Many Thanks!

Jenn

Figures - Blank (Exel 2007-2010 only).xlsm

Sorry to say but your excel is so unreadable that I will skip.
On the first sight there is so much different references that I doubt it can be much shorten.

Thanks for looking anyway :o)

It is a complicated one and that's as good as I could get it! The formulae have to take a number of different variables into account. I suppose the only way to shorten the formula would be to create an array of lookups to reference, though I am uncertain how beneficial that would be if these already do the job... would just be nice to make them more simple so that a successor could interpret them more easily.

You can do all calculation based on one sheet on that sheet, and then just to refer to one value in each sheet.
I think that would be shortest.

Whilst the formulae reference the previous day's total in and out (which is stock-checked on a monthly basis), the linked formulae looking at intake and production need to look at the SLA range for the particular category and calculate the amount of work that has fallen out of SLA from the total production and intake in the SLA period; it is due to this that the formulae need to look back on a daily basis, at up to 14 days of data. The idea of this figures sheet is to allow a front-end adviser/data entry clerk to enter intake and production for any particualr day and haev the sheet automatically calculate the SLAs, intake, production and volumes, once an initial amount has been entered.

If you can think of a better way to solve this problem, I am all ears! I am largely self-taught and have probably missed a few tricks here :o)

I'm not so artistic orianted

I would all lead on one sheet, not multiple sheets, with each day in it's own row.
If I want to have extracted day I would create sheet to "call" that row and then display it as you like.