+ Reply to Thread
Results 1 to 8 of 8

Calculate In-Between Numbers

  1. #1
    Brenda Rueter
    Guest

    Calculate In-Between Numbers

    User wants to combine within one formula:
    If C8 < x,then C6-90
    If C8 > x but under xx, C6-120
    If C8 > xx but < xxx, C6-150
    If C8 > xxx, C6-180

    Is there a function that will do the in-between parts for me?



  2. #2
    LanceB
    Guest

    RE: Calculate In-Between Numbers

    =IF(C8<A1,C6-90,IF(C8<A2,C6-120,IF(C8<A3,C6-150,C6-180)))

    a1 = x
    a2 = xx
    a3 = xxx

    "Brenda Rueter" wrote:

    > User wants to combine within one formula:
    > If C8 < x,then C6-90
    > If C8 > x but under xx, C6-120
    > If C8 > xx but < xxx, C6-150
    > If C8 > xxx, C6-180
    >
    > Is there a function that will do the in-between parts for me?
    >
    >
    >


  3. #3
    Brenda Rueter
    Guest

    Re: Calculate In-Between Numbers

    This does not take into account >x but < than xx.
    Let's say
    a1=100
    a2=200
    a3=300

    we want smaller than 300 but larger than 200. That's the part we're having
    trouble putting together. The straight nesting IF statement is no problem.

    "LanceB" <LanceB@discussions.microsoft.com> wrote in message
    news:EFCF8C15-90C1-4767-A48E-1F65B214D266@microsoft.com...
    > =IF(C8<A1,C6-90,IF(C8<A2,C6-120,IF(C8<A3,C6-150,C6-180)))
    >
    > a1 = x
    > a2 = xx
    > a3 = xxx
    >
    > "Brenda Rueter" wrote:
    >
    > > User wants to combine within one formula:
    > > If C8 < x,then C6-90
    > > If C8 > x but under xx, C6-120
    > > If C8 > xx but < xxx, C6-150
    > > If C8 > xxx, C6-180
    > >
    > > Is there a function that will do the in-between parts for me?
    > >
    > >
    > >




  4. #4
    Leo Heuser
    Guest

    Re: Calculate In-Between Numbers

    Brenda

    One way:

    =C6-(C8<x)*90-(AND(C8>=x,C8<xx))*120-(AND(C8>=xx,C8<xxx))*150-(C8>=xxx)*180

    assuming >=x, >=xx and >=xxx

    --
    Best Regards
    Leo Heuser

    Followup to newsgroup only please.

    "Brenda Rueter" <bkrueteNOSPAM@michigan.gov> skrev i en meddelelse
    news:eqqwPHWVFHA.3280@TK2MSFTNGP09.phx.gbl...
    > User wants to combine within one formula:
    > If C8 < x,then C6-90
    > If C8 > x but under xx, C6-120
    > If C8 > xx but < xxx, C6-150
    > If C8 > xxx, C6-180
    >
    > Is there a function that will do the in-between parts for me?
    >
    >




  5. #5
    arno
    Guest

    Re: Calculate In-Between Numbers

    Hi Brenda,

    > User wants to combine within one formula:
    > If C8 < x,then C6-90
    > If C8 > x but under xx, C6-120
    > If C8 > xx but < xxx, C6-150
    > If C8 > xxx, C6-180
    >
    > Is there a function that will do the in-between parts for me?


    lookup() will do the job for you, have a look in excel help. however,
    make sure you get correct results if eg. c8=xx (exactly xx, not
    smaller, not bigger).

    you need a table "data" like this one
    0 90
    x 120
    xx 150
    xxx 180


    then you can use
    =c6-lookup(c8,data,2,TRUE)


    arno


  6. #6
    Markus L
    Guest

    Re: Calculate In-Between Numbers

    "Brenda Rueter" <bkrueteNOSPAM@michigan.gov> wrote in message
    news:eqqwPHWVFHA.3280@TK2MSFTNGP09.phx.gbl...
    > User wants to combine within one formula:
    > If C8 < x,then C6-90
    > If C8 > x but under xx, C6-120
    > If C8 > xx but < xxx, C6-150
    > If C8 > xxx, C6-180


    Brenda, try this one:
    =IF(C8>xxx,C6-180,IF(C8>xx,C6-150,IF(C8>x,C6-120,C6-90)))
    Not elegant at all, a simple translation of your requirements.



  7. #7
    arno
    Guest

    Re: Calculate In-Between Numbers

    ooops:

    it should be VLOOKUP!

    arno

    > lookup() will do ...
    > then you can use
    > =c6-lookup(c8,data,2,TRUE)



  8. #8
    Brenda Rueter
    Guest

    Re: Calculate In-Between Numbers

    Thanks everyone. I'm working with the different solutions offered here.

    "Markus L" <uo9oew@lnubb.pbz> wrote in message
    news:Gb3ge.3$ae.3480@ns2.gip.net...
    > "Brenda Rueter" <bkrueteNOSPAM@michigan.gov> wrote in message
    > news:eqqwPHWVFHA.3280@TK2MSFTNGP09.phx.gbl...
    > > User wants to combine within one formula:
    > > If C8 < x,then C6-90
    > > If C8 > x but under xx, C6-120
    > > If C8 > xx but < xxx, C6-150
    > > If C8 > xxx, C6-180

    >
    > Brenda, try this one:
    > =IF(C8>xxx,C6-180,IF(C8>xx,C6-150,IF(C8>x,C6-120,C6-90)))
    > Not elegant at all, a simple translation of your requirements.
    >
    >




+ 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