+ Reply to Thread
Results 1 to 3 of 3

Simplify a Formula

  1. #1
    Registered User
    Join Date
    11-19-2012
    Location
    Manchester
    MS-Off Ver
    Excel 2003
    Posts
    14

    Simplify a Formula

    Hi

    I am currently using a formula throughout a cashflow that calculates the where in the cashflow expenditure should appear based on the start month and length of the event, using cost assumptions in a second sheet. Is there anyway I can simplify the below as it is currently in every cell of the cashflow (vast majority of the outcome being 0) as it is slowing down the sheet dramatically.

    =IF(AND('2. Plantation Costs'!$AH233='2. Plantation Costs'!$AK$4,'2. Plantation Costs'!$AI233<=R$5,SUM($L388:Q388)=0,SUM('2. Plantation Costs'!$AF233+'2. Plantation Costs'!$AI233)>R$5),'2. Plantation Costs'!$AJ233,0)+IF(AND('2. Plantation Costs'!$AH233='2. Plantation Costs'!$AK$5,'2. Plantation Costs'!$AI233<=R$5,SUM('2. Plantation Costs'!$AF233+'2. Plantation Costs'!$AI233)>R$5),'2. Plantation Costs'!$AJ233,0)+IF(AND('2. Plantation Costs'!$AH233='2. Plantation Costs'!$AK$6,'2. Plantation Costs'!$AI233<=R$5,'2. Plantation Costs'!$AF233>=R$5),'2. Plantation Costs'!$AJ233,0)+IF(AND('2. Plantation Costs'!$AH233='2. Plantation Costs'!$AK$7,'2. Plantation Costs'!$AI233<=R$5,SUM($L388:Q388)=0,SUM('2. Plantation Costs'!$AF233+'2. Plantation Costs'!$AI233)>R$5),'2. Plantation Costs'!$AJ233,0)

    This formula might not make much sense without the sheets, unfortunately I cannot attach the sheets due to confidentially for the client, here is it step by step as much as I can explain:

    IF(AND(Expenditure key = one off, start month <= start month at top of cashflow column, sum (previous columns for same cash flow line) = 0, sum(start month + number of months)<current month) , amount of expenditure, 0)

    The other 3 repeats are for the different expenditure keys, so if it is a fixed monthly cost, phased monthly or fixed annually.

    Thank you to anyone who has stayed with me through this long explanation. I am not sure if it is possible but anything that could speed this formula up, even do a logical test prior to embarking in the long formula to prevent his being calculated hundreds of times for the sake of a lot of 0's. I appreciate it is hard without the sheet.

    Thank you for your time

    Sam

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Simplify a Formula

    Can you post an example worksheet showing how your data is laid out? (Go Advanced > Manage Attachments)
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    03-19-2013
    Location
    Minneapolis, MN
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Simplify a Formula

    What if you group the If statements differently and interrigate the two Expenditure types with the same criteria together?
    Looks like '2. Plantation Costs'!$AK$4 & $AK$7 both check for sum (previous columns for same cash flow line) = 0 but the other two types of Expenditure Keys do not. For example:

    =IF(Or('2. Plantation Costs'!$AH233='2. Plantation Costs'!$AK$4,'2. Plantation Costs'!$AH233='2. Plantation Costs'!$AK$7),If(AND('2. Plantation Costs'!$AI233<=R$5,SUM($L388:Q388)=0,SUM('2. Plantation Costs'!$AF233+'2. Plantation Costs'!$AI233)>R$5),'2. Plantation Costs'!$AJ233,0))+IF(Or('2. Plantation Costs'!$AH233='2. Plantation Costs'!$AK$5,'2. Plantation Costs'!$AH233='2. Plantation Costs'!$AK$6),If(AND('2. Plantation Costs'!$AI233<=R$5,SUM('2. Plantation Costs'!$AF233+'2. Plantation Costs'!$AI233)>R$5),'2. Plantation Costs'!$AJ233,0))

    You may be able to consolidate the formula further if you check all the dates first and then use If statements to check for the other Expenditure Key & Sum=0 criteria.
    Sorry. This one is tough to visualize.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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