Can anyone please improve my formula?

Hi,

I was wondering if anyone could suggest an improvement to my formula?

I know its long (sorry) but it works. I have 3000 cells populated with it at the moment, here it is:


=IF(Y$5="NONE","",IF($W10<=$V$6,IF(D$2="CHANGE IN SITUATION",((IF($M$2="Days",SUMIF(TransactionDate,"=" & $X10,TransAmount),IF($M$2="Months",SUMIFS(TransAmount,TransactionDate,"<=" & EOMONTH($X10,0), TransactionDate, ">=" & (EOMONTH($X10,-1)+1)),IF($M$2="Years",SUMIFS(TransAmount,Year_Transaction,"=" & $X10)))))),IF(D$2="OVERALL SITUATION",(('Account Expenditure'!$L$23)+IF($M$2="Days",SUMIF(TransactionDate,"<=" & $X10,TransAmount),IF($M$2="Months",SUMIFS(TransAmount,TransactionDate,"<="&(EOMONTH($X10,0))),IF($M$2="Years",SUMIFS(TransAmount,Year_Transaction,"<=" & $X10))))),IF($F$3="Balance",(IF(D$2=$T$55,'Account Expenditure'!$L$8,IF(D$2=$T$56,'Account Expenditure'!$L$9,IF(D$2=$T$57,'Account Expenditure'!$L$10,IF(D$2=$T$58,'Account Expenditure'!$L$11,IF(D$2=$T$59,'Account Expenditure'!$L$12,IF(D$2=$T$60,'Account Expenditure'!$L$13,IF(D$2=$T$61,'Account Expenditure'!$L$14,IF(D$2=$T$62,'Account Expenditure'!$L$15,IF(D$2=$T$63,'Account Expenditure'!$L$16,IF(D$2=$T$64,'Account Expenditure'!$L$17,IF(D$2=$T$65,'Account Expenditure'!$L$18,IF(D$2=$T$66,'Account Expenditure'!$L$19,IF(D$2=$T$67,'Account Expenditure'!$L$20,IF(D$2=$T$68,'Account Expenditure'!$L$21,IF(D$2=$T$69,'Account Expenditure'!$L$22)))))))))))))))+IF($M$2="Days",SUMIFS(TransAmount,TransactionDate,"<=" & $X10,TransAccount,"=" & D$2),IF($M$2="Months",SUMIFS(TransAmount,TransactionDate,"<=" & $X11-1,TransAccount,"=" & D$2),IF($M$2="Years",SUMIFS(TransAmount,Year_Transaction,"<=" & $X10,TransAccount,"=" & D$2))))),IF($D$3="Catagory",IF($M$2="Days",SUMIFS(TransAmount,TransactionDate,"="&($X10),TransType,"<>"& IF($E$3="DD / SO","Manual",IF($E$3="Manual","DD / SO","All")),TransCatagory,"="&D$2,TransAmount,IF($F$3="Expenditure","<"&0,IF($F$3="Income",">"&0,IF($F$3="Cashflow","<>"&0)))),IF($M$2="Months",SUMIFS(TransAmount,TransactionDate,"<="&(EOMONTH($X10,0)),TransactionDate,">"&(EOMONTH($X10,-1)),TransType,"<>"& IF($E$3="DD / SO","Manual",IF($E$3="Manual","DD / SO","All")),TransCatagory,"="&D$2,TransAmount,IF($F$3="Expenditure","<"&0,IF($F$3="Income",">"&0,IF($F$3="Cashflow","<>"&0)))),IF($M$2="Years",SUMIFS(TransAmount,Year_Transaction,"=" & $X10,TransType,"<>"& IF($E$3="DD / SO","Manual",IF($E$3="Manual","DD / SO","All")),TransCatagory,"="&D$2,TransAmount,IF($F$3="Expenditure","<"&0,IF($F$3="Income",">"&0,IF($F$3="Cashflow","<>"&0))))))),IF($M$2="Days",SUMIFS(TransAmount,TransactionDate,"="&($X10),TransType,"<>"& IF($E$3="DD / SO","Manual",IF($E$3="Manual","DD / SO","All")),TransAccount,"="&D$2,TransAmount,IF($F$3="Expenditure","<"&0,IF($F$3="Income",">="&0,IF($F$3="Cashflow","<>"&0)))),IF($M$2="Months",SUMIFS(TransAmount,TransactionDate,"<="&(EOMONTH($X10,0)),TransactionDate,">"&(EOMONTH($X10,-1)),TransType,"<>"& IF($E$3="DD / SO","Manual",IF($E$3="Manual","DD / SO","All")),TransAccount,"="&D$2,TransAmount,IF($F$3="Expenditure","<"&0,IF($F$3="Income",">"&0,IF($F$3="Cashflow","<>"&0)))),IF($M$2="Years",SUMIFS(TransAmount,Year_Transaction,"=" & $X10,TransType,"<>"& IF($E$3="DD / SO","Manual",IF($E$3="Manual","DD / SO","All")),TransAccount,"="&D$2,TransAmount,IF($F$3="Expenditure","<"&0,IF($F$3="Income",">"&0,IF($F$3="Cashflow","<>"&0))))))))))),""))



Thank you in advance...

I have to say, that formula is just nuts.

It is not auditable, it is not maintainable, and if you have 3000 with that formula in, it is trance-inducing.

You have to re-design, use helper cells, use UDFs, use anything but get rid of it.

BTW, it also errors when I try to add it to a worksheet, and I don't have the skill or the patience to debug why - as I said it is not maintainable.

Thanks for your replies.

Sorry its nuts, I thought it was too but it was the only way so far I could get what I wanted without 20 different cells doing 20 different things and summing them all out - leading to about 60000 populated cells.

I will have to work on the workbook and then get it uploaded somewhere as it is 3.2MB with all of the other formulas and I will have to delete all of the sensitive values first and cut it down / put dummy values in.

It does not throw errors on my worksheet but I did spend a long time writing / debugging / adding to it.

Essentially it is intended on displaying different values and sums on demand of an end user selecting different perameters so that the graph on the sheet is truely dynamic.

I.e. the user can choose different accounts, types of expenditure, income, balances and cashflow over varying time periods and intervals and the graph changes to suit.

I will try to post it another day once I have had time to cut it down,

Thanks again.

Oh and I assume helper cells are for intermediate steps, please, what are UDFs?

UDFs are User Defined Functions. You can have a UDF that is called from a workheet formula and it can do many things, for instance if you have a test being repeated within your overall formual, you can write it in a UDF and simplify the formula by calling the UDF each time rather than repeating the complex functions.

But this is all conjecture, the correct answer, or more probably a GOOD answer, can only be given knowing what the business requirement(s) is(are), and what constraints are imposed. The best solution is then teh simplest design that gives the corerct answer, maybe helper cells, maybe UDFs, maybe pivot tables, who knows.

Originally Posted by B00105904

Sorry its nuts, I thought it was too but it was the only way so far I could get what I wanted without 20 different cells doing 20 different things and summing them all out - leading to about 60000 populated cells.

Whilst I think you need something far more radical than a few helper cells, it is more than likely than 60,000 simpler cells will be better in maintainability and in performance than 3,000 complex cells. This is because of the way the Excel calculation engine works.

Thanks,

I tried pivot tables but they did not give the required result, or I could not get them to, not in the way I wanted.
The helper cells would have helped debugging I suppose but they would have been 60k+ lol
I shall try as you have advised and read up on UDFs... thanks!

Originally Posted by B00105904

Thanks,

I tried pivot tables but they did not give the required result, or I could not get them to, not in the way I wanted.
The helper cells would have helped debugging I suppose but they would have been 60k+ lol
I shall try as you have advised and read up on UDFs... thanks!

As I said, 60,000 will probably be better than 3,000 in all ways.

Originally Posted by Bob Phillips

...it is more than likely than 60,000 simpler cells will be better in maintainability and in performance than 3,000 complex cells. This is because of the way the Excel calculation engine works.

Oh I had not known this, it might explain my performance issues I was having on lesser powered PCs, interesting.

I now wonder how hard it will be for me to break down the working if vulgar solution I have