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
Bookmarks