# Combing Multiple formulas

1. ## 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  Register To Reply

2. ## Re: Combing Multiple formulas

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)  Register To Reply

3. ## Re: Combing Multiple formulas

What is expected result? Give some examples.  Register To Reply

4. ## Re: Combing Multiple formulas 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,  Register To Reply

5. ## Re: Combing Multiple formulas

Hi AVK,
Please check my response to FDibbins   Register To Reply

6. ## Re: Combing Multiple formulas

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.  Register To Reply

7. ## Re: Combing Multiple formulas

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.  Register To Reply

8. ## Re: Combing Multiple formulas 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  Register To Reply

9. ## Re: Combing Multiple formulas 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.  Register To Reply

10. ## Re: Combing Multiple formulas

338 characters is counted for 1 country
and almost 10,000 characters in a single cell for 28 countries.

For each country, there are 3*3 differrent, long decimal values
For 28 countries, if they are in same logic, it is easy, but, there is 28*3*3 different decimal numbers.

Strongly advise a lookup table, as per my previous post.  Register To Reply

11. ## Re: Combing Multiple formulas

I agree with Quang PT, and suggested the same thing myself  Register To Reply

#### Thread Information

##### Users Browsing this Thread

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

#### Tags for this Thread #### 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