+ Reply to Thread
Results 1 to 19 of 19

HELP!!!!!

  1. #1
    Dougieg
    Guest

    HELP!!!!!

    Hi there...

    Is there any way to make the following formula shorter??? This is
    ridiculous, and I need the formula to evaluate such as it does... Sorry for
    the crosspost, but I am desperate!!!!


    =IF(F7>=27000,(19200/$G$4),IF(F7>=26500,(F7-100)*($G$4)/($G$4),IF(F7>=25000,(F7-200)*($G$4)/($G$4),IF(F6=6500,(24100)-(P3+P4)/($G$4)*($G$4),IF(F6=6000,(24100)-(P3+P4)/($G$4)*($G$4),IF(F6=5500,(24100)-(P3+P4)/($G$4)*($G$4),IF(F6=5000,(24100)-(P3+P4)/($G$4)*($G$4),IF(F7>=17000,(12000/$G$4),(F7-200)*($G$4)/($G$4)))))))))


    Thank you

    Doug



  2. #2
    Forum Contributor
    Join Date
    06-10-2004
    Location
    India
    Posts
    1,066
    This is all I could do:
    =IF(F7>=27000,19200/$G$4,IF(F7>=26500,F7-100,IF(F7>=25000,F7-200,IF(F6=6500,24100-(P3+P4),IF(F6=6000,24100-(P3+P4),IF(F6=5500,24100-(P3+P4),IF(F6=5000,24100-(P3+P4),IF(F7>=17000,12000/$G$4,F7-200))))))))

  3. #3
    Rowan
    Guest

    RE: HELP!!!!!

    You have two options. You could try using the OR statement in your existing
    formula:

    =IF(F7>=27000,(19200/$G$4),IF(F7>=26500,(F7-100)*($G$4)/($G$4),IF(F7>=25000,(F7-200)*($G$4)/($G$4),IF(OR(F6=6500,F6=6000,F6=5500,F6=5000),(24100)-(P3+P4)/($G$4)*($G$4),IF(F7>=17000,(12000/$G$4),(F7-200)*($G$4)/($G$4))))))

    Or you could set up a user defined function in a VBA module along the lines
    of:

    Function MyFunction(testVal1 As Range, testVal2 As Range, _
    Const1 As Range, Const2 As Range, const3 As Range)

    If testVal1 >= 27000 Then
    MyFunction = 19200 / Const1

    ElseIf testVal1 >= 26500 Then
    MyFunction = (testVal1 - 100) * (Const1 / Const1)

    ElseIf testVal1 >= 25000 Then
    MyFunction = (testVal1 - 200) * (Const1 / Const1)

    ElseIf testVal2 = 6500 Then
    MyFunction = 24100 - (Const2 + const3) / Const1 * Const1

    ElseIf testVal2 = 6000 Then
    MyFunction = 24100 - (Const2 + const3) / Const1 * Const1

    ElseIf testVal2 = 5500 Then
    MyFunction = 24100 - (Const2 + const3) / Const1 * Const1

    ElseIf testVal2 = 5000 Then
    MyFunction = 24100 - (Const2 + const3) / Const1 * Const1

    ElseIf testVal1 >= 17000 Then
    MyFunction = 12000 / Const1

    Else
    MyFunction = (testVal1 - 200) * (Const1 / Const1)
    End If

    End Function

    In your worksheet you could then enter =myfunction(F7,F6,G4,P3,P4) to get
    the same result.

    Regards
    Rowan

    "Dougieg" wrote:

    > Hi there...
    >
    > Is there any way to make the following formula shorter??? This is
    > ridiculous, and I need the formula to evaluate such as it does... Sorry for
    > the crosspost, but I am desperate!!!!
    >
    >
    > =IF(F7>=27000,(19200/$G$4),IF(F7>=26500,(F7-100)*($G$4)/($G$4),IF(F7>=25000,(F7-200)*($G$4)/($G$4),IF(F6=6500,(24100)-(P3+P4)/($G$4)*($G$4),IF(F6=6000,(24100)-(P3+P4)/($G$4)*($G$4),IF(F6=5500,(24100)-(P3+P4)/($G$4)*($G$4),IF(F6=5000,(24100)-(P3+P4)/($G$4)*($G$4),IF(F7>=17000,(12000/$G$4),(F7-200)*($G$4)/($G$4)))))))))
    >
    >
    > Thank you
    >
    > Doug
    >
    >
    >


  4. #4
    ^reaper^
    Guest

    Re: HELP!!!!!

    -----BEGIN PGP SIGNED MESSAGE-----
    Hash: SHA1

    While sipping absinthe, Dougieg heard a loud sucking noise coming
    from alt.2600, and hastily inscribed the following unintelligible
    Sanskrit in <news:V4tZd.684233$6l.513478@pd7tw2no>:

    > Hi there...
    >
    > Is there any way to make the following formula shorter??? This is
    > ridiculous, and I need the formula to evaluate such as it does...
    > Sorry for the crosspost, but I am desperate!!!!
    >
    > =IF(F7>=27000,(19200/$G$4),
    > IF(F7>=26500,(F7-100)*($G$4)/($G$4),
    > IF(F7>=25000,(F7-200)*($G$4)/($G$4),
    > IF(F6=6500,(24100)-(P3+P4)/($G$4)*($G$4),
    > IF(F6=6000,(24100)-(P3+P4)/($G$4)*($G$4),
    > IF(F6=5500,(24100)-(P3+P4)/($G$4)*($G$4),
    > IF(F6=5000,(24100)-(P3+P4)/($G$4)*($G$4),
    > IF(F7>=17000,(12000/$G$4),(F7-200)*($G$4)/($G$4)))))))))


    =IF(F7>=27000,19200/$G$4,IF(F7>=26500,F7-100,
    IF(F7>=25000,F7-200,
    IF(OR(F6=6500,F6=6000,F6=5500,F6=5000),24100-(P3+P4)/POWER($G$4,2),
    IF(F7>=17000,12000/$G$4,F7-200)))))

    ^reaper^

    -----BEGIN PGP SIGNATURE-----
    Version: PGPfreeware 6.5.8 for non-commercial use <http://www.pgp.com>

    iQA/AwUBQjZ8j1MeYoHj2dI5EQKQhQCguecIziU1KJlwky1n/K0QxAVhR3kAniPF
    tit9l9Q1eKzmh0WbNbWKbiJR
    =nt+n
    -----END PGP SIGNATURE-----

  5. #5
    Bob Phillips
    Guest

    Re: HELP!!!!!

    At the minimum,

    =IF(F7>=27000,19200/$G$4,IF(F7>=26500,(F7-100)*($G$4)/($G$4),IF(F7>=25000,(F
    7-200)*($G$4)/($G$4),IF(F6={6500,6000,5500,5000},(24100)-(P3+P4)/($G$4)*($G$
    4),IF(F7>=17000,(12000/$G$4),(F7-200)*($G$4)/($G$4))))))

    but you also seem to multiply and divide the same number by G4, so perhaps
    even

    =IF(F7>=27000,19200/$G$4,IF(F7>=26500,F7-100,IF(F7>=25000,F7-200,IF(F6={6500
    ,6000,5500,5000},24100-(P3+P4),IF(F7>=17000,12000/$G$4,F7-200)))))

    it gives the same answer in my tests


    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "Dougieg" <[email protected]> wrote in message
    news:V4tZd.684233$6l.513478@pd7tw2no...
    > Hi there...
    >
    > Is there any way to make the following formula shorter??? This is
    > ridiculous, and I need the formula to evaluate such as it does... Sorry

    for
    > the crosspost, but I am desperate!!!!
    >
    >
    >

    =IF(F7>=27000,(19200/$G$4),IF(F7>=26500,(F7-100)*($G$4)/($G$4),IF(F7>=25000,
    (F7-200)*($G$4)/($G$4),IF(F6=6500,(24100)-(P3+P4)/($G$4)*($G$4),IF(F6=6000,(
    24100)-(P3+P4)/($G$4)*($G$4),IF(F6=5500,(24100)-(P3+P4)/($G$4)*($G$4),IF(F6=
    5000,(24100)-(P3+P4)/($G$4)*($G$4),IF(F7>=17000,(12000/$G$4),(F7-200)*($G$4)
    /($G$4)))))))))
    >
    >
    > Thank you
    >
    > Doug
    >
    >




  6. #6
    Yomamma Bin Crawdaddin
    Guest

    Re: HELP!!!!!

    On Tue, 15 Mar 2005 09:08:29 -0000, "Bob Phillips"
    <[email protected]> wrote:

    >At the minimum,
    >
    >=IF(F7>=27000,19200/$G$4,IF(F7>=26500,(F7-100)*($G$4)/($G$4),IF(F7>=25000,(F
    >7-200)*($G$4)/($G$4),IF(F6={6500,6000,5500,5000},(24100)-(P3+P4)/($G$4)*($G$
    >4),IF(F7>=17000,(12000/$G$4),(F7-200)*($G$4)/($G$4))))))
    >
    >but you also seem to multiply and divide the same number by G4, so perhaps
    >even
    >
    >=IF(F7>=27000,19200/$G$4,IF(F7>=26500,F7-100,IF(F7>=25000,F7-200,IF(F6={6500
    >,6000,5500,5000},24100-(P3+P4),IF(F7>=17000,12000/$G$4,F7-200)))))
    >
    >it gives the same answer in my tests


    The answer is always beer....
    :-)
    --
    --------------------------------------------------------------------------------------------------------------------
    Yomamma bin Crawdaddin www.cotse.com
    Brotherhood (Vice Chairman)
    Anti Archangel #41 The difference between John Kerry and Ted Kennedy is
    Stalking Filth #69.5 that Ted Kennedy has at least one confirmed kill.
    >--|

    <(>>>><
    >--|


  7. #7
    Colonel_Flagg
    Guest

    Re: HELP!!!!!

    In article <[email protected]>, [email protected]
    says...
    > >=IF(F7>=27000,19200/$G$4,IF(F7>=26500,F7-100,IF(F7>=25000,F7-200,IF(F6={6500
    > >,6000,5500,5000},24100-(P3+P4),IF(F7>=17000,12000/$G$4,F7-200)))))
    > >
    > >it gives the same answer in my tests

    >
    > The answer is always beer....
    > :-)
    > --
    > --------------------------------------------------------------------------------------------------------------------
    > Yomamma bin Crawdaddin
    >



    >=IF(X)!=(Y)THEN(X)=BEER




    /CF

  8. #8
    Perfect Reign
    Guest

    Re: HELP!!!!!

    On Tue, 15 Mar 2005 07:03:06 -0500, Colonel_Flagg donned fireproof
    underwear and scratched on the wall:

    >> >=IF(F7>=27000,19200/$G$4,IF(F7>=26500,F7-100,IF(F7>=25000,F7-200,IF(F6={6500
    >> >,6000,5500,5000},24100-(P3+P4),IF(F7>=17000,12000/$G$4,F7-200)))))

    >> The answer is always beer....

    >
    >=IF(X)!=(Y)THEN(X)=BEER


    But I don't see where "cold pizza" fits in the equation.

    Couldn't we have something like
    =IF(X)!=(Y)+(FRIDGE)+($TIME)=(AM)THEN=BEER+PIZZA(COLD)

    --
    kai - www.perfectreign.com

    kai@yoda:~> format a:
    Error: The DOS concept of formatting disk media is screwed.



  9. #9
    Yomamma Bin Crawdaddin
    Guest

    Re: HELP!!!!!

    On Tue, 15 Mar 2005 07:03:06 -0500, Colonel_Flagg
    <colonel_flagg@_NOSOUPFORJ00_internetwarzone.org> wrote:

    >In article <[email protected]>, [email protected]
    >says...
    >> >=IF(F7>=27000,19200/$G$4,IF(F7>=26500,F7-100,IF(F7>=25000,F7-200,IF(F6={6500
    >> >,6000,5500,5000},24100-(P3+P4),IF(F7>=17000,12000/$G$4,F7-200)))))
    >> >
    >> >it gives the same answer in my tests

    >>
    >> The answer is always beer....
    >> :-)
    >> --
    >> --------------------------------------------------------------------------------------------------------------------
    >> Yomamma bin Crawdaddin
    >>

    >
    >
    >>=IF(X)!=(Y)THEN(X)=BEER

    >
    >
    >
    >/CF


    My kinda code....
    :-)
    --
    --------------------------------------------------------------------------------------------------------------------
    Yomamma bin Crawdaddin www.cotse.com
    Brotherhood (Vice Chairman)
    Anti Archangel #41 The difference between John Kerry and Ted Kennedy is
    Stalking Filth #69.5 that Ted Kennedy has at least one confirmed kill.
    >--|

    <(>>>><
    >--|


  10. #10
    Arvi Laanemets
    Guest

    Re: HELP!!!!!

    Hi

    On fly:
    =IF(AND(NOT(ISERROR(MATCH(F6,{6500;6000;5500;5000},0))),F7<25000),24100-P3+P
    4,CHOOSE(MATCH(F7,{0;17000;25000;26500;27000},1),F7-200,12000/$G$4,F7-200,F7
    -100,19200/$G$4))

    --
    When sending mail, use address arvil<at>tarkon.ee
    Arvi Laanemets



    "Dougieg" <[email protected]> wrote in message
    news:V4tZd.684233$6l.513478@pd7tw2no...
    > Hi there...
    >
    > Is there any way to make the following formula shorter??? This is
    > ridiculous, and I need the formula to evaluate such as it does... Sorry

    for
    > the crosspost, but I am desperate!!!!
    >
    >
    >

    =IF(F7>=27000,(19200/$G$4),IF(F7>=26500,(F7-100)*($G$4)/($G$4),IF(F7>=25000,
    (F7-200)*($G$4)/($G$4),IF(F6=6500,(24100)-(P3+P4)/($G$4)*($G$4),IF(F6=6000,(
    24100)-(P3+P4)/($G$4)*($G$4),IF(F6=5500,(24100)-(P3+P4)/($G$4)*($G$4),IF(F6=
    5000,(24100)-(P3+P4)/($G$4)*($G$4),IF(F7>=17000,(12000/$G$4),(F7-200)*($G$4)
    /($G$4)))))))))
    >
    >
    > Thank you
    >
    > Doug
    >
    >




  11. #11
    Dougieg
    Guest

    Re: HELP!!!!!

    Wow...

    Thank you all so much for the help....

    ALL the formulas given worked like a charm. So, I used the one that DIDN'T
    cross my eyes to bad!!!

    heh heh heh...

    Thank you all again!!!

    Doug


    "Dougieg" <[email protected]> wrote in message
    news:V4tZd.684233$6l.513478@pd7tw2no...
    > Hi there...
    >
    > Is there any way to make the following formula shorter??? This is
    > ridiculous, and I need the formula to evaluate such as it does... Sorry
    > for the crosspost, but I am desperate!!!!
    >
    >
    > =IF(F7>=27000,(19200/$G$4),IF(F7>=26500,(F7-100)*($G$4)/($G$4),IF(F7>=25000,(F7-200)*($G$4)/($G$4),IF(F6=6500,(24100)-(P3+P4)/($G$4)*($G$4),IF(F6=6000,(24100)-(P3+P4)/($G$4)*($G$4),IF(F6=5500,(24100)-(P3+P4)/($G$4)*($G$4),IF(F6=5000,(24100)-(P3+P4)/($G$4)*($G$4),IF(F7>=17000,(12000/$G$4),(F7-200)*($G$4)/($G$4)))))))))
    >
    >
    > Thank you
    >
    > Doug
    >




  12. #12
    Dougieg
    Guest

    Re: HELP!!!!!

    Hi again...

    Well... the formulas seemed to work, BUT, I still have problems...

    Let me explain, and show you the problem. I'm not so sure Excel can truly
    deal with it.
    What I am doing in the simplest form is converting Litres to KGs. This is to
    be able to
    get loading patterns for our tanker trailer fleet. At the present, the
    dispatchers have to
    pull out a calculator, and figure the litres for each compartment based on a
    specific product density.
    Our drivers load based on litres.

    e.g.: Gasoline weighs .7204kg per litre. Diesel weighs .8644kg.

    We have 7 products all with specific densities. Diesel is heaviest. Aviation
    fuel is lightest at .7015 per kg.
    I need to be able to mix and match compartments to product. And still not go
    over the above trailer weights, or compartment sizes. Diesel gives us our
    max weights for the trailers.
    The trailer will never reach max weight with Gas as it converts to more
    litres than can be held in specific compartments.


    Trailer compartments vary in size from 6500 litre capacity to 18000 litre
    capacity.
    Trailers may have from 2 to 6 compartments.

    Configurations are like so

    Front OO middle OOO back OO. The O represents axles.

    T1 XX T2 XX or XXX
    T1 XXX T2 XXX or XX

    Also... Trailer weights...
    Front OO Max weight 11273kg
    Middle Max 24200kg
    Back max 7830

    I am attaching a zip that contains what I have so far. May just need
    something I can't see.

    Further... the only entry for dispatch is to be Under TopMark... which is
    the actual compartment size...
    The other calculations below the first set reduce the front weights
    automatically based on Tractor/Lorry weights.
    So, they work as of now...

    Whew!!!

    Thanks Doug.


    "Dougieg" <[email protected]> wrote in message
    news:V4tZd.684233$6l.513478@pd7tw2no...
    > Hi there...
    >
    > Is there any way to make the following formula shorter??? This is
    > ridiculous, and I need the formula to evaluate such as it does... Sorry
    > for
    > the crosspost, but I am desperate!!!!
    >
    >
    > =IF(F7>=27000,(19200/$G$4),IF(F7>=26500,(F7-100)*($G$4)/($G$4),IF(F7>=25000,(F7-200)*($G$4)/($G$4),IF(F6=6500,(24100)-(P3+P4)/($G$4)*($G$4),IF(F6=6000,(24100)-(P3+P4)/($G$4)*($G$4),IF(F6=5500,(24100)-(P3+P4)/($G$4)*($G$4),IF(F6=5000,(24100)-(P3+P4)/($G$4)*($G$4),IF(F7>=17000,(12000/$G$4),(F7-200)*($G$4)/($G$4)))))))))
    >
    >
    > Thank you
    >
    > Doug
    >
    >






  13. #13
    Arvi Laanemets
    Guest

    Re: HELP!!!!!

    Hi

    Here is an example, how I would do it

    Create a sheet Articles, with a table on it (headers in row 1)
    Article, Density

    Enter into this table your products and their densities.
    Define named ranges
    Articles=OFFSET(Articles!$A$2,,,COUNTIF(Articles!$A:$A,"<>")-1,1)
    ArticleTbl=OFFSET(Articles!$A$2,,,COUNTIF(Articles!$A:$A,"<>")-1,2)

    Create a sheet Compartments, with a table
    CompartmentCode, Size, MaxWeight

    Enter all your possible compartment types and their parameters into table,
    like
    front1 9000 11273
    front2 8700 10030
    middle1 18000 20000
    etc. Compartments codes in table are unique.

    Define named ranges:
    Compartments=OFFSET(Compartments!$A$2,,,COUNTIF(Compartments!$A:$A,"<>")-1,1
    )
    CompartmentTbl=OFFSET(Compartments!$A$2,,,COUNTIF(Compartments!$A:$A,"<>")-1
    ,3)

    Create a sheet Configurations, with a table
    ConfigurationCode, FrontCompCode, FrontCompNum, MiddleCompCode,
    MiddleCompNum, BackCompCode, BackCompNum

    (I assume here, that compartments in same section are identic, i.e. you
    don't have p.e. 2 different front compartment in same train. When otherwise,
    then the design complicates somewhat, but the pattern remains same)

    For cells in columns FrontCompCode, MiddleCompCode and BackCompCode
    implement data validation list with source
    =Compartments
    For cells in columns FrontCompNum, MiddleCompNum and BackCompNum you can
    implement data validation list with source like
    =1,2,3

    Enter all possible train configurations you use at moment. As code use some
    easy-to read abverration, like "Front2(2)Middle1(3)Back2(1)"

    Define named ranges
    Configurations=OFFSET(Configurations!$A$2,,,COUNTIF(Configurations!$A:$A,"<>
    ")-1,1)
    ConfigurationTbl=OFFSET(Configurations!$A$2,,,COUNTIF(Configurations!$A:$A,"
    <>")-1,7)

    Create a sheet p.e. Transports (this will be your main working sheet), with
    a table (header row is row 2)
    Date, Transport, Configuration, Article group (group name in merged cells on
    row 1) with columns Front, Middle, and Back, Quantity group with same
    columns + Total, Weight group, again with same columns + Total, and
    optionally the group '% from max weight, again with columns Front, Middle,
    Back and Total.

    For Configuration column, implement data validation list with source
    =Configutations
    For Article column, implement data validation list with source
    =Articles

    Define named ranges
    BackArticleDensity=VLOOKUP(Transports!$F3,ArticleTbl,2,0)
    BackCompCode=VLOOKUP(Transports!$C3,ConfigurationTbl,6,0)
    BackCompMaxWeight=VLOOKUP(BackCompCode,CompartmentTbl,3,0)
    BackCompNum=VLOOKUP(Transports!$C3,ConfigurationTbl,7,0)
    BackCompSize=VLOOKUP(BackCompCode,CompartmentTbl,2,0)
    FrontArticleDensity=VLOOKUP(Transports!$D3,ArticleTbl,2,0)
    FrontCompCode=VLOOKUP(Transports!$C3,ConfigurationTbl,2,0)
    FrontCompMaxWeight=VLOOKUP(FrontCompCode,CompartmentTbl,3,0)
    FrontCompNum=VLOOKUP(Transports!$C3,ConfigurationTbl,3,0)
    FrontCompSize=VLOOKUP(FrontCompCode,CompartmentTbl,2,0)
    MiddleArticleDensity=VLOOKUP(Transports!$E3,ArticleTbl,2,0)
    MiddleCompCode=VLOOKUP(Transports!$C3,ConfigurationTbl,4,0)
    MiddleCompMaxWeight=VLOOKUP(MiddleCompCode,CompartmentTbl,3,0)
    MiddleCompNum=VLOOKUP(Transports!$C3,ConfigurationTbl,5,0)
    MiddleCompSize=VLOOKUP(MiddleCompCode,CompartmentTbl,2,0)

    Into cell G3 (Front+Quantity) enter the formula
    =IF(OR($C3="",$D3=""),"",MIN(FrontCompSize*FrontCompNum,ROUNDDOWN(FrontCompM
    axWeight*FrontCompNum/FrontArticleDensity,-1)))
    Into cell H3 (Middle+Quantity) enter the formula
    =IF(OR($C3="",$E3=""),"",MIN(MiddleCompSize*MiddleCompNum,ROUNDDOWN(MiddleCo
    mpMaxWeight*MiddleCompNum/MiddleArticleDensity,-1)))
    Into cell I3 (Back+Quantity) enter the formula
    =IF(OR($C3="",$F3=""),"",MIN(BackCompSize*BackCompNum;ROUNDDOWN(BackCompMaxW
    eight*BackCompNum/BackArticleDensity,-1)))
    Into cell J3 (Total+Quantity) enter the formula
    =IF(OR($C3="",SUM($G3:$I3)=0),"",ROUND(SUM($G3:$I3),0))
    Into cell K3 (Front+Weight) enter the formula
    =IF(OR($C3="",$D3=""),"",G3*FrontArticleDensity)
    Into cell L3 (Middle+Weight) enter the formula
    =IF(OR($C3="",$E3=""),"",H3*MiddleArticleDensity)
    Into cell M3 (Back+Weight) enter the formula
    =IF(OR($C3="",$F3=""),"",I3*BackArticleDensity)
    Into cell N3 (Total+Weight) enter the formula
    =IF(OR($C3="",SUM($K3:$M3)=0),"",ROUND(SUM($K3:$M3),0))
    When you created the optional group too, then into range O3:R3 enter the
    formulas
    =IF(OR($C3="",$D3=""),"",K3/(FrontCompMaxWeight*FrontCompNum))
    =IF(OR($C3="",$E3=""),"",L3/(MiddleCompMaxWeight*MiddleCompNum))
    =IF(OR($C3="",$F3=""),"",M3/(BackCompMaxWeight*BackCompNum))
    =IF(OR($C3="",SUM($K3:$M3)=0),"",N3/(FrontCompMaxWeight*FrontCompNum+MiddleC
    ompMaxWeight*MiddleCompNum+BackCompMaxWeight*BackCompNum))

    Format cells in row 3 as you need (optional group cells format as
    percentage). Copy all formulas and data validations down for some reasonable
    amount of rows. Finish the worksheet design (set fonts, borders, column
    widths etc.

    Start with data entry. Whenever you determine train configuration, and
    select articles for all sections, maximal possible quantities (up to section
    volume, but not over max allowed weight) and weights of fuel in every
    section are calculated.


    Arvi Laanemets


    "Dougieg" <[email protected]> wrote in message
    news:GvEZd.689105$6l.154793@pd7tw2no...
    > Hi again...
    >
    > Well... the formulas seemed to work, BUT, I still have problems...
    >
    > Let me explain, and show you the problem. I'm not so sure Excel can truly
    > deal with it.
    > What I am doing in the simplest form is converting Litres to KGs. This is

    to
    > be able to
    > get loading patterns for our tanker trailer fleet. At the present, the
    > dispatchers have to
    > pull out a calculator, and figure the litres for each compartment based on

    a
    > specific product density.
    > Our drivers load based on litres.
    >
    > e.g.: Gasoline weighs .7204kg per litre. Diesel weighs .8644kg.
    >
    > We have 7 products all with specific densities. Diesel is heaviest.

    Aviation
    > fuel is lightest at .7015 per kg.
    > I need to be able to mix and match compartments to product. And still not

    go
    > over the above trailer weights, or compartment sizes. Diesel gives us our
    > max weights for the trailers.
    > The trailer will never reach max weight with Gas as it converts to more
    > litres than can be held in specific compartments.
    >
    >
    > Trailer compartments vary in size from 6500 litre capacity to 18000 litre
    > capacity.
    > Trailers may have from 2 to 6 compartments.
    >
    > Configurations are like so
    >
    > Front OO middle OOO back OO. The O represents axles.
    >
    > T1 XX T2 XX or XXX
    > T1 XXX T2 XXX or XX
    >
    > Also... Trailer weights...
    > Front OO Max weight 11273kg
    > Middle Max 24200kg
    > Back max 7830
    >
    > I am attaching a zip that contains what I have so far. May just need
    > something I can't see.
    >
    > Further... the only entry for dispatch is to be Under TopMark... which is
    > the actual compartment size...
    > The other calculations below the first set reduce the front weights
    > automatically based on Tractor/Lorry weights.
    > So, they work as of now...
    >
    > Whew!!!
    >
    > Thanks Doug.
    >
    >
    > "Dougieg" <[email protected]> wrote in message
    > news:V4tZd.684233$6l.513478@pd7tw2no...
    > > Hi there...
    > >
    > > Is there any way to make the following formula shorter??? This is
    > > ridiculous, and I need the formula to evaluate such as it does... Sorry
    > > for
    > > the crosspost, but I am desperate!!!!
    > >
    > >
    > >

    =IF(F7>=27000,(19200/$G$4),IF(F7>=26500,(F7-100)*($G$4)/($G$4),IF(F7>=25000,
    (F7-200)*($G$4)/($G$4),IF(F6=6500,(24100)-(P3+P4)/($G$4)*($G$4),IF(F6=6000,(
    24100)-(P3+P4)/($G$4)*($G$4),IF(F6=5500,(24100)-(P3+P4)/($G$4)*($G$4),IF(F6=
    5000,(24100)-(P3+P4)/($G$4)*($G$4),IF(F7>=17000,(12000/$G$4),(F7-200)*($G$4)
    /($G$4)))))))))
    > >
    > >
    > > Thank you
    > >
    > > Doug
    > >
    > >

    >
    >
    >




  14. #14
    Arvi Laanemets
    Guest

    Re: HELP!!!!!

    Hi

    NB! When defining named ranges after Transports sheet is created, it is
    essential that you have selected any cell on row, to where you will
    reference in range definitions. In my example, a cell in row 3 on Transports
    sheet must be selected!

    The reason for this is, that those names are dynamic, and the value returned
    depends on row address, from where they are called.

    --
    When sending mail, use address arvil<at>tarkon.ee
    Arvi Laanemets



  15. #15
    Dougieg
    Guest

    Re: HELP!!!!!

    Thank you Avri... I am attempting to give it a try...

    That is after looking up what the functions that I am NOT familiar with
    actually do... should be a good workout for a junior Excel type person.

    Thanks

    Doug



    "Arvi Laanemets" <[email protected]> wrote in message
    news:[email protected]...
    > Hi
    >
    > NB! When defining named ranges after Transports sheet is created, it is
    > essential that you have selected any cell on row, to where you will
    > reference in range definitions. In my example, a cell in row 3 on
    > Transports
    > sheet must be selected!
    >
    > The reason for this is, that those names are dynamic, and the value
    > returned
    > depends on row address, from where they are called.
    >
    > --
    > When sending mail, use address arvil<at>tarkon.ee
    > Arvi Laanemets
    >
    >




  16. #16
    Dougieg
    Guest

    Re: HELP!!!!!

    bah... I suck!!!

    Avri... I need just one answer here that may just accommodate what I need...


    Take these cells...

    14000 13800 This is OK
    17000 17000 This is OK
    14000 13800 This is OK
    11000 XXXX This is wrong!!!

    Think as column one being F5 to F8, and column 2 as G5 to G8.
    Based on total of 26650, I need to find the value proper value XXXX.
    based on the 11000, it should be 10800. Only because the maximum in the cell
    above can be is 16800.
    If the 13800 cell goes higher to its max of 16800 then I need to make
    appropriate changes to XXXX to keep within
    the 26650 restriction. Of course... if the cell that has 11000 changes to
    something lower, then it would continue to
    regress 200 from its total. Howerever... if the number increases to a point
    that will accommodate the total of 26650, then I need to be able to
    calculate what should be in XXXX, to a max of 26650. Below is what I had
    come up with, but it does not evaluate correctly... My XXXX value can be
    larger than what can be accommodated by the number to its left.
    I really don't think Excel can do this... or I do not understand how.


    IF(AND(G7>=16800,F8<=9850),26651-G7,IF(AND(F7>=16800,F8>=9850),(9850),IF(AND(F7<16999,F8<=9850),(16800-G7+9850),IF(AND(F7<16999,F8>=9850),(16800-G7+9850),(F8-200))))))))

    BTW... the formula above was inspired by you... remember how long it was at
    first??

    Doug




    "Dougieg" <[email protected]> wrote in message
    news:tmXZd.696047$8l.614779@pd7tw1no...
    > Thank you Avri... I am attempting to give it a try...
    >
    > That is after looking up what the functions that I am NOT familiar with
    > actually do... should be a good workout for a junior Excel type person.
    >
    > Thanks
    >
    > Doug
    >
    >
    >
    > "Arvi Laanemets" <[email protected]> wrote in message
    > news:[email protected]...
    >> Hi
    >>
    >> NB! When defining named ranges after Transports sheet is created, it is
    >> essential that you have selected any cell on row, to where you will
    >> reference in range definitions. In my example, a cell in row 3 on
    >> Transports
    >> sheet must be selected!
    >>
    >> The reason for this is, that those names are dynamic, and the value
    >> returned
    >> depends on row address, from where they are called.
    >>
    >> --
    >> When sending mail, use address arvil<at>tarkon.ee
    >> Arvi Laanemets
    >>
    >>

    >
    >




  17. #17
    Arvi Laanemets
    Guest

    Re: HELP!!!!!

    Hi


    "Dougieg" <[email protected]> wrote in message
    news:th3_d.702755$6l.42735@pd7tw2no...
    > bah... I suck!!!
    >
    > Avri... I need just one answer here that may just accommodate what I

    need...
    >
    >
    > Take these cells...
    >
    > 14000 13800 This is OK
    > 17000 17000 This is OK
    > 14000 13800 This is OK
    > 11000 XXXX This is wrong!!!
    >
    > Think as column one being F5 to F8, and column 2 as G5 to G8.
    > Based on total of 26650, I need to find the value proper value XXXX.
    > based on the 11000, it should be 10800. Only because the maximum in the

    cell
    > above can be is 16800.
    > If the 13800 cell goes higher to its max of 16800 then I need to make
    > appropriate changes to XXXX to keep within
    > the 26650 restriction. Of course... if the cell that has 11000 changes to
    > something lower, then it would continue to
    > regress 200 from its total. Howerever... if the number increases to a

    point
    > that will accommodate the total of 26650, then I need to be able to
    > calculate what should be in XXXX, to a max of 26650. Below is what I had
    > come up with, but it does not evaluate correctly... My XXXX value can be
    > larger than what can be accommodated by the number to its left.
    > I really don't think Excel can do this... or I do not understand how.



    I understood nothing! What do you exactly want?
    The entry in cell G8 + entry in which cell mustn't exceed 26650? Is it
    G7+G8, or F7+F8, or F8G8? Or all of them?
    (The first step to find a solution is to define the task!)


    >
    >
    >

    IF(AND(G7>=16800,F8<=9850),26651-G7,IF(AND(F7>=16800,F8>=9850),(9850),IF(AND
    (F7<16999,F8<=9850),(16800-G7+9850),IF(AND(F7<16999,F8>=9850),(16800-G7+9850
    ),(F8-200))))))))

    Try to analyze this formula in next way:
    If AND(G7>=16800,F8<=9850) then 26651-G7;
    Else If AND(F7>=16800,F8>=9850) then 9850;
    Else If AND(F7<16999,F8<=9850) then 6800-G7+9850=26650-G7;
    Else If AND(F7<16999,F8>=9850) then 6800-G7+9850=26650-G7;
    Else F8-200

    With 3th and 4th conditions, same result is returned, so you can join them:
    If AND(G7>=16800,F8<=9850) then 26651-G7;
    Else If AND(F7>=16800,F8>=9850) then 9850;
    Else If F7<16999 then 6800-G7+9850=26650-G7;
    Else F8-200



    Is this what you wanted?

    --
    When sending mail, use address arvil<at>tarkon.ee
    Arvi Laanemets



  18. #18
    Hans van Eynsbergen
    Guest

    Re: HELP!!!!!

    On Tue, 15 Mar 2005 11:30:24 GMT, Yomamma Bin Crawdaddin surprised everyone
    on Usenet by scribbling:

    > On Tue, 15 Mar 2005 09:08:29 -0000, "Bob Phillips"
    > <[email protected]> wrote:
    >
    >>At the minimum,
    >>
    >>=IF(F7>=27000,19200/$G$4,IF(F7>=26500,(F7-100)*($G$4)/($G$4),IF(F7>=25000,(F
    >>7-200)*($G$4)/($G$4),IF(F6={6500,6000,5500,5000},(24100)-(P3+P4)/($G$4)*($G$
    >>4),IF(F7>=17000,(12000/$G$4),(F7-200)*($G$4)/($G$4))))))
    >>
    >>but you also seem to multiply and divide the same number by G4, so perhaps
    >>even
    >>
    >>=IF(F7>=27000,19200/$G$4,IF(F7>=26500,F7-100,IF(F7>=25000,F7-200,IF(F6={6500
    >>,6000,5500,5000},24100-(P3+P4),IF(F7>=17000,12000/$G$4,F7-200)))))
    >>
    >>it gives the same answer in my tests

    >
    > The answer is always beer....
    > :-)


    Bwahahahahahahhahaaaaaaaaaaaaaaaaa !!!!
    --
    Hans van Eynsbergen
    I may not have gone where I intended to go, but I think I have ended up
    where I needed to be.

  19. #19
    Amedee Van Gasse
    Guest

    Re: HELP!!!!!

    Yomamma Bin Crawdaddin shared this with us in microsoft.public.excel:

    > On Tue, 15 Mar 2005 09:08:29 -0000, "Bob Phillips"
    > <[email protected]> wrote:
    >
    > > At the minimum,
    > >
    > > =IF(F7>=27000,19200/$G$4,IF(F7>=26500,(F7-100)*($G$4)/($G$4),IF(F7>=
    > > 25000,(F
    > > 7-200)*($G$4)/($G$4),IF(F6={6500,6000,5500,5000},(24100)-(P3+P4)/($G
    > > $4)*($G$ 4),IF(F7>=17000,(12000/$G$4),(F7-200)*($G$4)/($G$4))))))
    > >
    > > but you also seem to multiply and divide the same number by G4, so
    > > perhaps even
    > >
    > > =IF(F7>=27000,19200/$G$4,IF(F7>=26500,F7-100,IF(F7>=25000,F7-200,IF(
    > > F6={6500
    > > ,6000,5500,5000},24100-(P3+P4),IF(F7>=17000,12000/$G$4,F7-200)))))
    > >
    > > it gives the same answer in my tests

    >
    > The answer is always beer....
    > :-)
    > --
    > ----------------------------------------------------------------------
    > ---------------------------------------------- Yomamma bin Crawdaddin
    > www.cotse.com Brotherhood (Vice Chairman)
    > Anti Archangel #41 The difference
    > between John Kerry and Ted Kennedy is Stalking Filth #69.5
    > that Ted Kennedy has at least one confirmed kill.
    > > --|

    > <(>>>><
    >
    > > --|


    I thought the answer was 47.

    PS: Your sigmonster is handicapped. Please add a blank to your sig
    separator.

    --
    Amedee Van Gasse using XanaNews 1.17.3.1
    If it has an "X" in the name, it must be Linux?

    How To Ask Questions The Smart Way
    http://www.catb.org/~esr/faqs/smart-questions.html
    How to Report Bugs Effectively
    http://www.chiark.greenend.org.uk/~sgtatham/bugs.html
    Only ask questions with yes/no answers if you want "yes" or "no" as the
    answer.
    http://homepages.tesco.net/~J.deBoyn...-with-yes-or-n
    o-answers.html

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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