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.


--
Sterling
------------------------------------------------------------------------
Sterling's Profile: http://www.excelforum.com/member.php...o&userid=29554
View this thread: http://www.excelforum.com/showthread...hreadid=492585

need help coming up with a formula.
 

One way:

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


In article ,
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.

need help coming up with a formula.
 

=AVERAGE(IF((A1:A200)*(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" wrote in
message ...

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

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

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

need help coming up with a formula.
 

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


--
Sterling
------------------------------------------------------------------------
Sterling's Profile: http://www.excelforum.com/member.php...o&userid=29554
View this thread: http://www.excelforum.com/showthread...hreadid=492585

need help coming up with a formula.
 

One more question if I may:

I've used this formula and it works great:

=AVERAGE(IF((A1:A200)*(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

need help coming up with a formula.
 

One way

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

--

HTH

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


"Sterling" wrote in
message ...

One more question if I may:

I've used this formula and it works great:

=AVERAGE(IF((A1:A200)*(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

need help coming up with a formula.
 

Thank you. That formula worked great.


--
Sterling
------------------------------------------------------------------------
Sterling's Profile: http://www.excelforum.com/member.php...o&userid=29554
View this thread: http://www.excelforum.com/showthread...hreadid=492585