Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match then lookup | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
New user needing help with coming up with a formula | New Users to Excel | |||
IF & VLOOKUP FORMULA | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |