Combing Multiple formulas

Hi, I have around 28 formulas that do the same job based on different conditions. I have combined them with and function. But when I use the resultant formula as formula in excel then it shows the error as shown in the screenshot. I have attached the formula and sample excel sheet as well.

My requirement is to combine them in single formula otherwise this simple task becomes a long job where I have to calculate multiple values one by one. I hope you understand my objective.

Please have a look and let me know if we can overcome this situation.


Snapshot2.PNG
Snapshot.PNG

Originally Posted by FDibbins

You have told us that you are having problem with a formula you are building, but have not explained exactly what you are trying to do?

I also see that part of your formula references cells that are blank. Please provide some sample data for us to work with, as well as show what your expected outcome should be (and why, if it is not obvious)

Pardon from my side. This long formula takes different values based on set criteria and convert them into different integers. This long formula is composed of around 28 small formulas. The long formula is more than 8192 charactrs so the excel show up the error as shown in the screenshot. The individual formulas and the resultant formula I provided are correct but due to excel limitation of 8192 characters I can't combine them and use them in single long formula. If you notice, I have combined multiple formulas with & operator. For easy understanding for complete formula, please check the text file in attachment where I have added spaces.

When formula in C2 starts working then I will drag the formula in that column to calculate rest of the values.

Please let me know if I have missed something in clarification.

Thanks and Regards,

Hi AVK,
Please check my response to FDibbins

Originally Posted by FDibbins

Still not really telling us what you are trying to do - what is that formula supposed to do?

I have a feeling that you need to create a table somewhere where you run the basic calcs (or hard code the nswers), then use vlookup or INDEX/MATCH to return the answer you need.

Hi, I have attached the snapshot and new sample file. Please ignore the previous excel file and consider this one in attachment. I have taken part of that long formula and calculated a value for your reference as an example.

Seconldy, as a side not, Out of those 28 sub formulas, one sub formula will calculate integer value and the rest will return "false" text because they won't meet the condition. So the net result would be like

e.g 170FALSEFALSEFALSE.....


Then I will manually replace "False" and I will get 170 value.


Please let me know about your thoughts if that clarifies it now or I advise me please.

Thanks

Originally Posted by bebo021999

Basically for each country, each range of value:
=10^(n1*LOG^2+n2*LOG+n3)

with each set of (n1, n2, n3 ) differentia each country.

You should create a lookup table with country in column and n accross, like this.


I created a sample for Denmark. The others need to be filled in then copy C3 down.

Hi, my main objective is to form single long formula which is more than 8200 characters and calculate different values as I explained in the thread. I have more than 300K values for for different countries. Then I will calculate the values using and dragging the formula in column. I have also attached new sample file for your understanding. Please have a look and you will understand it easily.