+ Reply to Thread
Results 1 to 4 of 4

How to reduces Nested IF code(144 Nested IF) to achive same functionality

  1. #1
    Registered User
    Join Date
    02-08-2014
    Location
    pakistan
    MS-Off Ver
    Excel 2007
    Posts
    6

    How to reduces Nested IF code(144 Nested IF) to achive same functionality

    Hello, I am coding for Pressure units convertor in Excel2007, I have write the nested IF function about 144 nested IF function's levels, but Excel 2007 supports up to 64 nested functions, tell me the solution, How i reduce this code to achieve the same functionality or alternate of Nested If function that can achieve the same task.
    Code is given below

    Nested IF AND Function For Pressure Conversion
    =IF(AND((G21="in.H2O"),(G22="in.H2O")),ROUNDUP((I21*1),2),IF(AND((G21="in.H2O"),(G22="in.Hg")),ROUNDUP((I21*0.07354),2),IF(AND((G21="in.H2O"),(G22="mmH2O")),ROUNDUP((I21*25.4),2),IF(AND((G21="in.H2O"),(G22="mmHg/Torr")),ROUNDUP((I21*1.868),2),IF(AND((G21="in.H2O"),(G22="Kg/Cm2")),ROUNDUP((I21*0.0025396),2),IF(AND((G21="in.H2O"),(G22="bar")),ROUNDUP((I21*0.002489),2),IF(AND((G21="in.H2O"),(G22="mbar")),ROUNDUP((I21*2.489),2),IF(AND((G21="in.H2O"),(G22="Pa")),ROUNDUP((I21*248.9),2),IF(AND((G21="in.H2O"),(G22="KPa")),ROUNDUP((I21*0.2489),2),IF(AND((G21="in.H2O"),(G22="MPa")),ROUNDUP((I21*0.0002489),2),IF(AND((G21="in.H2O"),(G22="PSI")),ROUNDUP((I21*0.03612),2), IF(AND((G21="in.H2O"),(G22="atm")),ROUNDUP((I21*0.00245832),2), IF(AND((G21="in.Hg"),(G22="in.H2O")),ROUNDUP((I21*13.598),2),IF(AND((G21="in.Hg"),(G22="in.Hg")),ROUNDUP((I21*1),2),IF(AND((G21="in.Hg"),(G22="mmH2O")),ROUNDUP((I21*345.315),2),IF(AND((G21="in.Hg"),(G22="mmHg/Torr")),ROUNDUP((I21*25.4),2),IF(AND((G21="in.Hg"),(G22="Kg/Cm2")),ROUNDUP((I21*0.034532),2),IF(AND((G21="in.Hg"),(G22="bar")),ROUNDUP((I21*0.03385),2),IF(AND((G21="in.Hg"),(G22="mbar")),ROUNDUP((I21*33.85),2),IF(AND((G21="in.Hg"),(G22="Pa")),ROUNDUP((I21*3385),2),IF(AND((G21="in.Hg"),(G22="KPa")),ROUNDUP((I21*3.385),2),IF(AND((G21="in.Hg"),(G22="MPa")),ROUNDUP((I21*0.003385),2), IF(AND((G21="in.Hg"),(G22="PSI")),ROUNDUP((I21*0.491154),2),IF(AND((G21="in.Hg"),(G22="atm")),ROUNDUP((I21*0.0334211),2),IF(AND((G21="mmH2O"),(G22="in.H2O")),ROUNDUP((I21*0.0393701),2),IF(AND((G21="mmH2O"),(G22="in.Hg")),ROUNDUP((I21*0.00289590),2),IF(AND((G21="mmH2O"),(G22="mmH2O")),ROUNDUP((I21*1),2),IF(AND((G21="mmH2O"),(G22="mmHg/Torr")),ROUNDUP((I21*0.0735559),2),IF(AND((G21="mmH2O"),(G22="Kg/Cm2")),ROUNDUP((I21*0.0001),2),IF(AND((G21="mmH2O"),(G22="bar")),ROUNDUP((I21*0.0000980665),2),IF(AND((G21="mmH2O"),(G22="mbar")),ROUNDUP((I21*0.0980665),2),IF(AND((G21=”mmH2O"),(G22="Pa")),ROUNDUP((I21*9.80665),2),IF(AND((G21="mmH2O"),(G22="KPa")),ROUNDUP((I21*0.00980665),2),IF(AND((G21="mmH2O"),(G22="MPa")),ROUNDUP((I21*0.00000980665),2),IF(AND((G21="mmH2O"),(G22="PSI")),ROUNDUP((I21*0.00142233),2),IF(AND((G21="mmH2O"),(G22="atm")),ROUNDUP((I21*0.0000967841),2),IF(AND((G21="mmHg/Torr"),(G22="in.H2O")),ROUNDUP((I21*0.5353),2),IF(AND((G21="mmHg/Torr"),(G22="in.Hg")),ROUNDUP((I21*0.03937),2),IF(AND((G21="mmHg/Torr"),(G22="mmH2O")),ROUNDUP((I21*13.5951),2),IF(AND((G21="mmHg/Torr"),(G22="mmHg/Torr")),ROUNDUP((I21*1),2),IF(AND((G21="mmHg/Torr"),(G22="Kg/Cm2")),ROUNDUP((I21*0.001359),2),IF(AND((G21="mmHg/Torr"),(G22="bar")),ROUNDUP((I21*0.00133),2),IF(AND((G21="mmHg/Torr"),(G22="mbar")),ROUNDUP((I21*1.3328),2),IF(AND((G21="mmHg/Torr"),(G22="Pa")),ROUNDUP((I21*133.28),2),IF(AND((G21="mmHg/Torr"),(G22="KPa")),ROUNDUP((I21*0.13328),2),IF(AND((G21="mmHg/Torr"),(G22="MPa")),ROUNDUP((I21*0.00013328),2),IF(AND((G21="mmHg/Torr"),(G22="PSI")),ROUNDUP((I21*0.01934),2),IF(AND((G21="mmHg/Torr"),(G22="atm")),ROUNDUP((I21*0.00131579),2), IF(AND((G21="Kg/Cm2"),(G22="in.H2O")),ROUNDUP((I21*393.76),2),IF(AND((G21="Kg/Cm2"),(G22="in.Hg")),ROUNDUP((I21*28.958),2),IF(AND((G21="Kg/Cm2"),(G22="mmH2O")),ROUNDUP((I21*10000),2),IF(AND((G21="Kg/Cm2"),(G22="mmHg/Torr")),ROUNDUP((I21*735.54),2),IF(AND((G21="Kg/Cm2"),(G22="Kg/Cm2")),ROUNDUP((I21*1),2),IF(AND((G21="Kg/Cm2"),(G22="bar")),ROUNDUP((I21*0.9804),2),IF(AND((G21="Kg/Cm2"),(G22="mbar")),ROUNDUP((I21*908.4),2),IF(AND((G21="Kg/Cm2"),(G22="Pa")),ROUNDUP((I21*98040),2),IF(AND((G21=”Kg/Cm2"),(G22="KPa")),ROUNDUP((I21*98.04),2),IF(AND((G21="Kg/Cm2"),(G22="MPa")),ROUNDUP((I21*0.09804),2),IF(AND((G21="Kg/Cm2"),(G22="PSI")),ROUNDUP((I21*14.223),2),IF(AND((G21="Kg/Cm2"),(G22="atm")),ROUNDUP((I21*0.967841),2),IF(AND((G21="bar"),(G22="in.H2O")),ROUNDUP((I21*401.65),2),IF(AND((G21="bar"),(G22="in.Hg")),ROUNDUP((I21*29.54),2),IF(AND((G21="bar"),(G22="mmH2O")),ROUNDUP((I21*10197.2),2),IF(AND((G21="bar"),(G22="mmHg/Torr")),ROUNDUP((I21*750),2),IF(AND((G21="bar"),(G22="Kg/Cm2")),ROUNDUP((I21*1.02),2),IF(AND((G21="bar"),(G22="bar")),ROUNDUP((I21*1),2),IF(AND((G21="bar"),(G22="mbar")),ROUNDUP((I21*1000),2),IF(AND((G21="bar"),(G22="Pa")),ROUNDUP((I21*100000),2),IF(AND((G21="bar"),(G22="KPa")),ROUNDUP((I21*100),2),IF(AND((G21="bar"),(G22="MPa")),ROUNDUP((I21*0.10),2),IF(AND((G21="bar"),(G22="PS")),ROUNDUP((I21*14.503861),2),IF(AND((G21="bar"),(G22="atm")),ROUNDUP((I21*0.986923),2),IF(AND((G21="mbar"),(G22="in.H2O")),ROUNDUP((I21*0.4018),2),IF(AND((G21="mbar"),(G22="in.Hg")),ROUNDUP((I21*0.02954),2),IF(AND((G21="mbar"),(G22="mmH2O")),ROUNDUP((I21*10.1972),2),IF(AND((G21="mbar"),(G22="mmHg/Torr")),ROUNDUP((I21*0.75028),2),IF(AND((G21="mbar"),(G22="Kg/Cm2")),ROUNDUP((I21*0.00102),2),IF(AND((G21="mbar"),(G22="bar")),ROUNDUP((I21*0.001),2),IF(AND((G21="mbar"),(G22="mbar")),ROUNDUP((I21*1),2),IF(AND((G21="mbar"),(G22="Pa")),ROUNDUP((I21*100),2),IF(AND((G21="mbar"),(G22="KPa")),ROUNDUP((I21*0.1),2),IF(AND((G21=" mbar"),(G22="MPa")),ROUNDUP((I21*0.0001),2), IF(AND((G21=" mbar"),(G22="PSI")),ROUNDUP((I21*0.0145),2),IF(AND((G21=" mbar"),(G22="atm")),ROUNDUP((I21*0.000986923),2),IF(AND((G21="Pa"),(G22="in.H2O")),ROUNDUP((I21*0.004018),2),IF(AND((G21="Pa"),(G22="in.Hg")),ROUNDUP((I21*0.0002954),2),IF(AND((G21="Pa"),(G22="mmH2O")),ROUNDUP((I21*0.101972),2),IF(AND((G21="Pa"),(G22="mmHg/Torr")),ROUNDUP((I21*0.0075),2),IF(AND((G21="Pa"),(G22="Kg/Cm2")),ROUNDUP((I21*0.0000102),2),IF(AND((G21="Pa"),(G22="bar")),ROUNDUP((I21*0.00001),2),IF(AND((G21="Pa"),(G22="mbar")),ROUNDUP((I21*0.01),2),IF(AND((G21="Pa"),(G22="Pa")),ROUNDUP((I21*1),2),IF(AND((G21="Pa"),(G22="KPa")),ROUNDUP((I21*0.001),2),IF(AND((G21="Pa"),(G22="MPa")),ROUNDUP((I21*0.000001),2),IF(AND((G21="Pa"),(G22="PSI")),ROUNDUP((I21*0.000145),2),IF(AND((G21="Pa"),(G22="atm")),ROUNDUP((I21*0.00000986923),2), IF(AND((G21=" KPa"),(G22="in.H2O")),ROUNDUP((I21*4.01463),2), IF(AND((G21="KPa"),(G22="in.Hg")),ROUNDUP((I21*0.295300),2),IF(AND((G21="Pa"),(G22="mmH2O")),ROUNDUP((I21*101.972),2),IF(AND((G21="KPa"),(G22="mmHg/Torr")),ROUNDUP((I21*7.50062),2),IF(AND((G21="KPa"),(G22="Kg/Cm2")),ROUNDUP((I21*0.0101972),2),IF(AND((G21="KPa"),(G22="bar")),ROUNDUP((I21*0.01),2),IF(AND((G21="KPa"),(G22="mbar")),ROUNDUP((I21*10),2),IF(AND((G21="KPa"),(G22="Pa")),ROUNDUP((I21*1000),2),IF(AND((G21=”KPa"),(G22="KPa")),ROUNDUP((I21*1),2),IF(AND((G21="KPa"),(G22="MPa")),ROUNDUP((I21*0.001),2),IF(AND((G21="KPa"),(G22="PSI")),ROUNDUP((I21*0.145038),2),IF(AND((G21="KPa"),(G22="atm")),ROUNDUP((I21*0.0098693),2),IF(AND((G21="MPa"),(G22="in.H2O")),ROUNDUP((I21*4014.63),2),IF(AND((G21="MPa"),(G22="in.Hg")),ROUNDUP((I21*295.3),2),IF(AND((G21="MPa"),(G22="mmH2O")),ROUNDUP((I21*101972),2),IF(AND((G21="MPa"),(G22="mmHg/Torr")),ROUNDUP((I21*7500.62),2),IF(AND((G21="MPa"),(G22="Kg/Cm2")),ROUNDUP((I21*10.1972),2),IF(AND((G21="MPa"),(G22="bar")),ROUNDUP((I21*10),2),IF(AND((G21="MPa"),(G22="mbar")),ROUNDUP((I21*10000),2),IF(AND((G21="MPa"),(G22="Pa")),ROUNDUP((I21*1000000),2),IF(AND((G21="MPa"),(G22="KPa")),ROUNDUP((I21*1000),2),IF(AND((G21="MPa"),(G22="MPa")),ROUNDUP((I21*1),2),IF(AND((G21="MPa"),(G22="PSI")),ROUNDUP((I21*145.038),2),IF(AND((G21=" MPa"),(G22="atm")),ROUNDUP((I21*9.86923),2), IF(AND((G21="PSI"),(G22="in.H2O")),ROUNDUP((I21*27.71),2),IF(AND((G21="PSI"),(G22="in.Hg")),ROUNDUP((I21*2.036),2),IF(AND((G21="PSI"),(G22="mmH2O")),ROUNDUP((I21*703.1),2),IF(AND((G21="PSI"),(G22="mmHg/Torr")),ROUNDUP((I21*51.75),2),IF(AND((G21="PSI"),(G22="Kg/Cm2")),ROUNDUP((I21*0.703),2),IF(AND((G21="PSI"),(G22="bar")),ROUNDUP((I21*0.689),2),IF(AND((G21="PSI"),(G22="mbar")),ROUNDUP((I21*68.95),2),IF(AND((G21="PSI"),(G22="Pa")),ROUNDUP((I21*6895),2),IF(AND((G21="PSI"),(G22="KPa")),ROUNDUP((I21*6.895),2),IF(AND((G21="PSI"),(G22="MPa")),ROUNDUP((I21*0.006895),2),IF(AND((G21="PSI"),(G22="PSI")),ROUNDUP((I21*1),2),IF(AND((G21="PSI"),(G22="atm")),ROUNDUP((I21*0.0680460),2),IF(AND((G21="atm"),(G22="in.H2O")),ROUNDUP((I21*406.86),2),IF(AND((G21="atm"),(G22="in.Hg")),ROUNDUP((I21*29.9213),2),IF(AND((G21="atm"),(G22="mmH2O")),ROUNDUP((I21*10332.3),2),IF(AND((G21="atm"),(G22="mmHg/Torr")),ROUNDUP((I21*760),2),IF(AND((G21="atm"),(G22="Kg/Cm2")),ROUNDUP((I21*1.03325),2),IF(AND((G21="atm"),(G22="bar")),ROUNDUP((I21*1.01325),2),IF(AND((G21”atm"),(G22="mbar")),ROUNDUP((I21*1012.95),2),IF(AND((G21="atm"),(G22="Pa")),ROUNDUP((I21*101325),2),IF(AND((G21="atm"),(G22="KPa")),ROUNDUP((I21*101.325),2),IF(AND((G21="atm"),(G22="MPa")),ROUNDUP((I21*0.101325),2),IF(AND((G21="atm"),(G22="PSI")),ROUNDUP((I21*14.7),2),IF(AND((G21="atm"),(G22="atm")),ROUNDUP((I21*1),2), ("Select Regtrd Unit")))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

  2. #2
    Registered User
    Join Date
    04-20-2013
    Location
    Woking, England
    MS-Off Ver
    Excel 2003, Excel 2007
    Posts
    34

    Re: How to reduces Nested IF code(144 Nested IF) to achive same functionality

    Hi

    May I suggest you look at the CONVERT function, presuming you are using Excel 2007 or above, and remove the units that are covered by that function .

    Then, where necessary create a table of the remaining conversion units, which should reduce the formula to :-
    Please Login or Register  to view this content.

    hth
    Last edited by ukmikeb; 02-08-2014 at 09:10 AM.
    Mike

  3. #3
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: How to reduces Nested IF code(144 Nested IF) to achive same functionality

    Create a lookup table and refer to that in your formula.
    In the attached file I started one for you: Jobseeker_PressureUnits_RON_v1a.xlsx

    Then you can use a formula like this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    I hope that helps.
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  4. #4
    Registered User
    Join Date
    02-08-2014
    Location
    pakistan
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: How to reduces Nested IF code(144 Nested IF) to achive same functionality

    Hello,
    Your suggestion support well,
    Thanks

+ 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. Replies: 0
    Last Post: 10-01-2012, 05:54 AM
  2. Nested IF/OR formula or vba code instead?
    By rachbendy in forum Excel General
    Replies: 2
    Last Post: 04-02-2009, 04:56 AM
  3. Nested IF Functionality
    By Callithumpina in forum Excel General
    Replies: 3
    Last Post: 02-06-2009, 04:49 AM
  4. help on a nested countif/nested if formula
    By vickiemc in forum Excel General
    Replies: 2
    Last Post: 08-13-2008, 08:29 AM
  5. Alternative Code - Nested Ifs
    By gizoku00 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-10-2008, 11:16 PM

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