# need help coming up with a formula.

1. ## 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. ## 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. ## 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
>

4. ## 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
>
>

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

6. Wow that was fast. And just what I needed. Thank you all for the help.

7. 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. ## 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
>

9. Thank you. That formula worked great.

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

#### 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