+ Reply to Thread
Results 1 to 12 of 12

Simplifying a formula: Consolidate formula to calculate SLA

  1. #1
    Registered User
    Join Date
    07-29-2011
    Location
    Manchester, UK
    MS-Off Ver
    Excel 2010
    Posts
    43

    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.

    Many thanks for any help you can provide

    Jenn
    Last edited by zbor; 10-28-2012 at 05:20 PM.

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,406

    Re: Assistance to simplify a formula please

    Can you upload example workbook?

  3. #3
    Registered User
    Join Date
    07-29-2011
    Location
    Manchester, UK
    MS-Off Ver
    Excel 2010
    Posts
    43

    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. #4
    Registered User
    Join Date
    07-29-2011
    Location
    Manchester, UK
    MS-Off Ver
    Excel 2010
    Posts
    43

    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. #5
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,406

    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. #6
    Registered User
    Join Date
    07-29-2011
    Location
    Manchester, UK
    MS-Off Ver
    Excel 2010
    Posts
    43

    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. #7
    Registered User
    Join Date
    07-29-2011
    Location
    Manchester, UK
    MS-Off Ver
    Excel 2010
    Posts
    43

    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. #8
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,406

    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. #9
    Registered User
    Join Date
    07-29-2011
    Location
    Manchester, UK
    MS-Off Ver
    Excel 2010
    Posts
    43

    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. #10
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,406

    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. #11
    Registered User
    Join Date
    07-29-2011
    Location
    Manchester, UK
    MS-Off Ver
    Excel 2010
    Posts
    43

    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. #12
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,406

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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