+ Reply to Thread
Results 1 to 9 of 9

need help coming up with a formula.

  1. #1
    Registered User
    Join Date
    12-11-2005
    Posts
    4

    need help coming up with a formula.

    I know just enough in Excel that I know I can get the info I want but not enough to know how to do it. Any help would be much appreciated. I am working with 2 columns of data, for example:

    Days in Stock____________Cost

    2_____________________$300
    21____________________$400
    117___________________$600
    8_____________________$400
    37____________________$500
    78____________________$500

    What I want to calculate is the average cost for 0-10 days in stock (($300 + $400)/2= $350), the average cost for 11-60 days in stock, and the average cost for 60+ days in stock.

    What sort of formula would best calculate this? TIA.

  2. #2
    JE McGimpsey
    Guest

    Re: need help coming up with a formula.

    One way:

    =SUMIF(A:A,"<=10",B:B)/COUNTIF(A:A,"<=10")


    In article <Sterling.1zwbym_1134339009.3479@excelforum-nospam.com>,
    Sterling <Sterling.1zwbym_1134339009.3479@excelforum-nospam.com>
    wrote:

    > I know just enough in Excel that I know I can get the info I want but
    > not enough to know how to do it. Any help would be much appreciated. I
    > am working with 2 columns of data, for example:
    >
    > Days in Stock____________Cost
    >
    > 2_____________________$300
    > 21____________________$400
    > 117___________________$600
    > 8_____________________$400
    > 37____________________$500
    > 78____________________$500
    >
    > What I want to calculate is the average cost for 0-10 days in stock
    > (($300 + $400)/2= $350), the average cost for 11-60 days in stock, and
    > the average cost for 60+ days in stock.
    >
    > What sort of formula would best calculate this? TIA.


  3. #3
    Bob Phillips
    Guest

    Re: need help coming up with a formula.

    =AVERAGE(IF((A1:A20>0)*(A1:A20<=10),B1:B20))

    as an array formula, so commit with Ctrl-Shift-Enter.

    Just change the 0 and the 10 for other ranges.

    --

    HTH

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


    "Sterling" <Sterling.1zwbym_1134339009.3479@excelforum-nospam.com> wrote in
    message news:Sterling.1zwbym_1134339009.3479@excelforum-nospam.com...
    >
    > I know just enough in Excel that I know I can get the info I want but
    > not enough to know how to do it. Any help would be much appreciated. I
    > am working with 2 columns of data, for example:
    >
    > Days in Stock____________Cost
    >
    > 2_____________________$300
    > 21____________________$400
    > 117___________________$600
    > 8_____________________$400
    > 37____________________$500
    > 78____________________$500
    >
    > What I want to calculate is the average cost for 0-10 days in stock
    > (($300 + $400)/2= $350), the average cost for 11-60 days in stock, and
    > the average cost for 60+ days in stock.
    >
    > What sort of formula would best calculate this? TIA.
    >
    >
    > --
    > Sterling
    > ------------------------------------------------------------------------
    > Sterling's Profile:

    http://www.excelforum.com/member.php...o&userid=29554
    > View this thread: http://www.excelforum.com/showthread...hreadid=492585
    >




  4. #4
    Ron Coderre
    Guest

    RE: need help coming up with a formula.

    Try this:
    If your data is in Cells A1:B7

    C1: From
    D1: Thru
    E1: AvgCost
    C2: 0
    D2: 10

    C3: 11
    D3: 60

    C4: 61
    D4: 1000

    E2: =AVERAGE(IF(--($A$2:$A$7>=C2)*($A$2:$A$7<=D2),$B$2:$B$7))
    Copy that formula down

    Is that what you're looking for?

    ***********
    Regards,
    Ron


    "Sterling" wrote:

    >
    > I know just enough in Excel that I know I can get the info I want but
    > not enough to know how to do it. Any help would be much appreciated. I
    > am working with 2 columns of data, for example:
    >
    > Days in Stock____________Cost
    >
    > 2_____________________$300
    > 21____________________$400
    > 117___________________$600
    > 8_____________________$400
    > 37____________________$500
    > 78____________________$500
    >
    > What I want to calculate is the average cost for 0-10 days in stock
    > (($300 + $400)/2= $350), the average cost for 11-60 days in stock, and
    > the average cost for 60+ days in stock.
    >
    > What sort of formula would best calculate this? TIA.
    >
    >
    > --
    > Sterling
    > ------------------------------------------------------------------------
    > Sterling's Profile: http://www.excelforum.com/member.php...o&userid=29554
    > View this thread: http://www.excelforum.com/showthread...hreadid=492585
    >
    >


  5. #5
    Ron Coderre
    Guest

    Important addition: [Ctrl]+[Shift]+[Enter]

    For
    E2: =AVERAGE(IF(--($A$2:$A$7>=C2)*($A$2:$A$7<=D2),$B$2:$B$7))

    You need to commit that array formula by holding down the [Ctrl] and [Shift]
    keys when you press [Enter].

    ***********
    Regards,
    Ron


    "Ron Coderre" wrote:

    > Try this:
    > If your data is in Cells A1:B7
    >
    > C1: From
    > D1: Thru
    > E1: AvgCost
    > C2: 0
    > D2: 10
    >
    > C3: 11
    > D3: 60
    >
    > C4: 61
    > D4: 1000
    >
    > E2: =AVERAGE(IF(--($A$2:$A$7>=C2)*($A$2:$A$7<=D2),$B$2:$B$7))
    > Copy that formula down
    >
    > Is that what you're looking for?
    >
    > ***********
    > Regards,
    > Ron
    >
    >
    > "Sterling" wrote:
    >
    > >
    > > I know just enough in Excel that I know I can get the info I want but
    > > not enough to know how to do it. Any help would be much appreciated. I
    > > am working with 2 columns of data, for example:
    > >
    > > Days in Stock____________Cost
    > >
    > > 2_____________________$300
    > > 21____________________$400
    > > 117___________________$600
    > > 8_____________________$400
    > > 37____________________$500
    > > 78____________________$500
    > >
    > > What I want to calculate is the average cost for 0-10 days in stock
    > > (($300 + $400)/2= $350), the average cost for 11-60 days in stock, and
    > > the average cost for 60+ days in stock.
    > >
    > > What sort of formula would best calculate this? TIA.
    > >
    > >
    > > --
    > > Sterling
    > > ------------------------------------------------------------------------
    > > Sterling's Profile: http://www.excelforum.com/member.php...o&userid=29554
    > > View this thread: http://www.excelforum.com/showthread...hreadid=492585
    > >
    > >


  6. #6
    Registered User
    Join Date
    12-11-2005
    Posts
    4
    Wow that was fast. And just what I needed. Thank you all for the help.

  7. #7
    Registered User
    Join Date
    12-11-2005
    Posts
    4
    One more question if I may:

    I've used this formula and it works great:

    =AVERAGE(IF((A1:A20>0)*(A1:A20<=10),B1:B20))

    Now, how do I incorporate a second set of data say in columns C and D. For example, the first set of data, as I posted, would be for cars and the second set would be for trucks. Using the above formula I can find out the average cost for 0-10 day cars and 0-10 day trucks. How would I set up the formula to come up with 0-10 day vehicles?

    TIA again.

  8. #8
    Bob Phillips
    Guest

    Re: need help coming up with a formula.

    One way

    =(SUMPRODUCT(--(A1:A20>0),--(A1:A20<=10),B1:B20)+SUMPRODUCT(--(C1:C20>0),--(
    C1:C20<=10),D1:D20))/(SUMPRODUCT(--(A1:A20>0),--(A1:A20<=10))+SUMPRODUCT(--(
    C1:C20>0),--(C1:C20<=10)))

    --

    HTH

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


    "Sterling" <Sterling.1zxxda_1134413406.2452@excelforum-nospam.com> wrote in
    message news:Sterling.1zxxda_1134413406.2452@excelforum-nospam.com...
    >
    > One more question if I may:
    >
    > I've used this formula and it works great:
    >
    > =AVERAGE(IF((A1:A20>0)*(A1:A20<=10),B1:B20))
    >
    > Now, how do I incorporate a second set of data say in columns C and D.
    > For example, the first set of data, as I posted, would be for cars and
    > the second set would be for trucks. Using the above formula I can find
    > out the average cost for 0-10 day cars and 0-10 day trucks. How would I
    > set up the formula to come up with 0-10 day -vehicles-?
    >
    > TIA again.
    >
    >
    > --
    > Sterling
    > ------------------------------------------------------------------------
    > Sterling's Profile:

    http://www.excelforum.com/member.php...o&userid=29554
    > View this thread: http://www.excelforum.com/showthread...hreadid=492585
    >




  9. #9
    Registered User
    Join Date
    12-11-2005
    Posts
    4
    Thank you. That formula worked great.

+ 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