Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I make this happen
I am not used to working with Excel. This is what I want to happen, but I am
not sure how to translate it into Excel. I Have a colum with order sizes of our companies product. So colum A is values between 1 and 2000 units. I want to see this: Total Units sold = X % of X that were orders of 1-150 units % of X that were orders of 150-300 units % of X that were orders of 300-600 unites % of X that were orders of 600-1000 % of X that were orders of 1000+ I can input them manually by typing "=SUM(A1:A12)/X" but each time I add an order I have to redo them all. So what I need is something like: =SUM(ColumA) WHERE VALUE is equal to or less than 150 Can anyone help me with this? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I make this happen
Let's say that column A has the order sizes.
Let's also say that your data is in rows 2-2000. There is a way to dynamically determine this, but that needs to wait. Total units sold = SUM (A$2:A$2000) Let's say that in J2 you have 1 and in K2 you have 150 (your range for the first group) To count the # of orders between 1 and 150, enter this: =SUMPRODUCT(--(A$2:A$2000J2),--(A$2:A$2000<K2)) To dynamically determine the range, look up the OFFSET function and create a named range. Maybe someone else can assist with that. "m@" wrote: I am not used to working with Excel. This is what I want to happen, but I am not sure how to translate it into Excel. I Have a colum with order sizes of our companies product. So colum A is values between 1 and 2000 units. I want to see this: Total Units sold = X % of X that were orders of 1-150 units % of X that were orders of 150-300 units % of X that were orders of 300-600 unites % of X that were orders of 600-1000 % of X that were orders of 1000+ I can input them manually by typing "=SUM(A1:A12)/X" but each time I add an order I have to redo them all. So what I need is something like: =SUM(ColumA) WHERE VALUE is equal to or less than 150 Can anyone help me with this? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I make this happen
m@
I would suggest entering the following in 4 different cells. They need to be entered as array formulas so press ctrl+shift+enter to enter them. Adjust your range to suit percent below 150 =SUMPRODUCT(--(A1:A7<=150),A1:A7)/SUM(A1:A7) percent greater than 150 but less than 300 =SUMPRODUCT(--(A1:A7150),--(A1:A7<=300),A1:A7)/SUM(A1:A7) percent greater than 300 and less than 600 =SUMPRODUCT(--(A1:A7300),--(A1:A7<=600),A1:A7)/SUM(A1:A7) percent greater than 600 and less than 1000 =SUMPRODUCT(--(A1:A7600),--(A1:A7<=1000),A1:A7)/SUM(A1:A7) percent greater than 1000 =SUMPRODUCT(--(A1:A71000),A1:A7)/SUM(A1:A7) "m@" wrote in message ... I am not used to working with Excel. This is what I want to happen, but I am not sure how to translate it into Excel. I Have a colum with order sizes of our companies product. So colum A is values between 1 and 2000 units. I want to see this: Total Units sold = X % of X that were orders of 1-150 units % of X that were orders of 150-300 units % of X that were orders of 300-600 unites % of X that were orders of 600-1000 % of X that were orders of 1000+ I can input them manually by typing "=SUM(A1:A12)/X" but each time I add an order I have to redo them all. So what I need is something like: =SUM(ColumA) WHERE VALUE is equal to or less than 150 Can anyone help me with this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I make a 3-dimensional plot in Excel? | Charts and Charting in Excel | |||
Can I make formulas more flexible? | Excel Discussion (Misc queries) | |||
How do I make each row add up seperatly? Please make it easy... | New Users to Excel | |||
make hidden window or workbook visible without specify the name | Excel Worksheet Functions | |||
make cell contents equal to null value - not blank, but empty | Excel Worksheet Functions |