+ Reply to Thread
Results 1 to 11 of 11

Combing Multiple formulas

  1. #1
    Forum Contributor
    Join Date
    07-19-2017
    Location
    usa
    MS-Off Ver
    2007
    Posts
    107

    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
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,901

    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)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Excel 2007 & 2003
    Posts
    2,888

    Re: Combing Multiple formulas

    What is expected result? Give some examples.


    atul


    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

  4. #4
    Forum Contributor
    Join Date
    07-19-2017
    Location
    usa
    MS-Off Ver
    2007
    Posts
    107

    Re: Combing Multiple formulas

    Quote Originally Posted by FDibbins View Post
    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,

  5. #5
    Forum Contributor
    Join Date
    07-19-2017
    Location
    usa
    MS-Off Ver
    2007
    Posts
    107

    Re: Combing Multiple formulas

    Hi AVK,
    Please check my response to FDibbins

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,901

    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.

  7. #7
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,841

    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.
    Attached Images Attached Images
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    07-19-2017
    Location
    usa
    MS-Off Ver
    2007
    Posts
    107

    Re: Combing Multiple formulas

    Quote Originally Posted by FDibbins View Post
    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
    Attached Images Attached Images
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    07-19-2017
    Location
    usa
    MS-Off Ver
    2007
    Posts
    107

    Re: Combing Multiple formulas

    Quote Originally Posted by bebo021999 View Post
    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.
    Attached Images Attached Images
    Attached Files Attached Files

  10. #10
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,841

    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.
    Last edited by bebo021999; 12-22-2020 at 04:03 AM.

  11. #11
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,901

    Re: Combing Multiple formulas

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Combing two formulas
    By JimmerB in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 06-30-2019, 12:17 PM
  2. [SOLVED] Combing tow formulas
    By Khaldon in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-06-2016, 08:48 AM
  3. [SOLVED] Combing two IF formulas
    By Chris Fawcett in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-24-2015, 10:16 AM
  4. combing multiple worksheets in a workbook
    By runner in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-31-2014, 03:54 PM
  5. [SOLVED] Combing multiple IF Statements.
    By Bobby82 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-21-2012, 01:45 AM
  6. Combing other cells into formulas
    By JakeMann in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-24-2012, 11:47 AM
  7. Excel 2007 : Combing formulas
    By ccampbell14 in forum Excel General
    Replies: 6
    Last Post: 07-12-2012, 02:15 PM

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