# Simplifying a formula: Consolidate formula to calculate SLA

1. ## Simplifying a formula: Consolidate formula to calculate SLA

If possible, please can someone assist me in simplifying the formula I have just created to calculate SLA on a day to day basis. I have tried to break it down as simply as possible below:

``Please Login or Register  to view this content.``

For reference, here is the text version:

=IF( Yesterday Production Total = 0 , 0 ,

IF(AND( SLA Production < SLA Intake , Yesterday In SLA > 0 , Yesterday Out SLA - Yesterday Production + SUM( SLA Intake – SLA Production ) > 0 , SUM( Yesterday In SLA + Yesterday Intake – SUM( SLA Intake – SLA Production ))<0), Yesterday OUT SLA – Yesterday Production + SUM( SLA Intake – SLA Production ) + Yesterday In SLA + Yesterday Intake – SUM( SLA Intake – SLA Production ),

IF(AND( SLA Production < SLA Intake , Yesterday In SLA > 0 , Yesterday Out SLA – Yesterday Production + SUM( SLA Intake – SLA Production )>0),IF( Yesterday Out SLA – Yesterday Production + SUM( SLA Intake – SLA Production )> Yesterday Out SLA + Yesterday In SLA – Yesterday Production , Yesterday Out SLA + Yesterday In SLA – Yesterday Production , Yesterday Out SLA – Yesterday Production + SUM( SLA Intake – SLA Production )),

IF( Yesterday Out SLA – Yesterday Production <0,0, Yesterday Out SLA – Yesterday Production ))))

Any help would be greatly appreciated! This one has been hell to transpose, as I am sure you can imagine :o)

I am also currently working on a similar formula to calculate inside SLA, which I may also need cutting down, if it is possible.

Jenn

3. ## Re: Assistance to simplify a formula please

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

4. ## Re: Assistance to simplify a formula please

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.``

5. ## Re: Simplifying a formula: Consolidate formula to calculate SLA

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.

6. ## Re: Simplifying a formula: Consolidate formula to calculate SLA

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)

7. ## Re: Simplifying a formula: Consolidate formula to calculate SLA

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

8. ## Re: Simplifying a formula: Consolidate formula to calculate SLA

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.

9. ## Re: Simplifying a formula: Consolidate formula to calculate SLA

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.

10. ## Re: Simplifying a formula: Consolidate formula to calculate SLA

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.

11. ## Re: Simplifying a formula: Consolidate formula to calculate SLA

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)

12. ## Re: Simplifying a formula: Consolidate formula to calculate SLA

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.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1