Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum of "everything but me".
I have a sheet of inventory broken down by location. Some items are related
to other items, for instance, there are nuts and bolts that are needed to fasten girders. We maintain this relationship through the primary IDs in our associated database; if an item in question has a "underid", then it is related to some other item. For instance: thing related to packages items/pack Girders 10 Bolts Girders 1 10 Nuts Girders 1 10 Hoses 5 Nozzles Hoses 2 The formula I'm trying to write is to make sure that I don't have a shortfall of any one item at any one location. In the example above, we have enough nuts and bolts for the girders, but we don't have enough nozzles for the hoses. So what I need to know is whether or not the quantity of any given item is enough to "cover" any other related item. Mathemetically it's (total quant of all related items)-(total quant of this item) If that number is bigger than zero, we're cool. Now this looks like something you could do with a SUMPRODUCT, right? Well no such luck (I think). As you can see, the database is lazy and doesn't put in default values for items per pack if it's only 1. So I need to put an ISNULL(quantity,1) in there. Is there a way to do this in SUMPRODUCT? Then I looked at using just SUM for this, but I can't figure out how this works. In the SUMPRODUCT help there is a very confusing statement; "The preceding example returns the same result as the formula SUM(A2:B4*C2:D4) entered as an array" So I tried a few experiments with SUM, but I can't figure out what it's doing. I put two columns of four numbers in a sheet and used =SUM(A1:A4*B1:B4), and the result was simply the value of A*B, it didn't sum up over the rows. Am I missing something obvious here? The other problem is the one that really has me stumped. The grouping for summing up is basically if my underid is in either the first OR second column. That is, the total is anything that has my underid as its underid, or anything that has my underid as its ID. The good news is that it's basically an ISNULL(underid, ID), because we want to use ID only if Anyone have some suggestions? BTW, the sheet is 1500 rows long, and about 50 columns wide, so major surgery in terms of adding new related columns might not be easy. Maury |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum of "everything but me".
Maury,
Lots of questions there - but I'll take a stab at some of them. First, with your data in A1:D6 and a list of your unique things in K2:K6, the following formula in L2 (and copied to L3:L6) will give you the correct count of the unique things: =SUMPRODUCT(--($A$2:$A$6=$K2),($C$2:$C$6),($D$2:$D$6))+SUMPRODUC T(--($A$2:$A$6=$K2),--($D$2:$D$6=0),($C$2:$C$6)) Second, your SUM formula is an array function and must be committed (entered) with Ctrl-Shift-Enter to produce {sum(____:_____)}. if you just hit enter, you will not have an array function. "Maury Markowitz" wrote: I have a sheet of inventory broken down by location. Some items are related to other items, for instance, there are nuts and bolts that are needed to fasten girders. We maintain this relationship through the primary IDs in our associated database; if an item in question has a "underid", then it is related to some other item. For instance: thing related to packages items/pack Girders 10 Bolts Girders 1 10 Nuts Girders 1 10 Hoses 5 Nozzles Hoses 2 The formula I'm trying to write is to make sure that I don't have a shortfall of any one item at any one location. In the example above, we have enough nuts and bolts for the girders, but we don't have enough nozzles for the hoses. So what I need to know is whether or not the quantity of any given item is enough to "cover" any other related item. Mathemetically it's (total quant of all related items)-(total quant of this item) If that number is bigger than zero, we're cool. Now this looks like something you could do with a SUMPRODUCT, right? Well no such luck (I think). As you can see, the database is lazy and doesn't put in default values for items per pack if it's only 1. So I need to put an ISNULL(quantity,1) in there. Is there a way to do this in SUMPRODUCT? Then I looked at using just SUM for this, but I can't figure out how this works. In the SUMPRODUCT help there is a very confusing statement; "The preceding example returns the same result as the formula SUM(A2:B4*C2:D4) entered as an array" So I tried a few experiments with SUM, but I can't figure out what it's doing. I put two columns of four numbers in a sheet and used =SUM(A1:A4*B1:B4), and the result was simply the value of A*B, it didn't sum up over the rows. Am I missing something obvious here? The other problem is the one that really has me stumped. The grouping for summing up is basically if my underid is in either the first OR second column. That is, the total is anything that has my underid as its underid, or anything that has my underid as its ID. The good news is that it's basically an ISNULL(underid, ID), because we want to use ID only if Anyone have some suggestions? BTW, the sheet is 1500 rows long, and about 50 columns wide, so major surgery in terms of adding new related columns might not be easy. Maury |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum of "everything but me".
One moe thing...
I'm not sure how you want to count the "related to" items. Post your expected answers and someone will be able to help you out. Jim "Maury Markowitz" wrote: I have a sheet of inventory broken down by location. Some items are related to other items, for instance, there are nuts and bolts that are needed to fasten girders. We maintain this relationship through the primary IDs in our associated database; if an item in question has a "underid", then it is related to some other item. For instance: thing related to packages items/pack Girders 10 Bolts Girders 1 10 Nuts Girders 1 10 Hoses 5 Nozzles Hoses 2 The formula I'm trying to write is to make sure that I don't have a shortfall of any one item at any one location. In the example above, we have enough nuts and bolts for the girders, but we don't have enough nozzles for the hoses. So what I need to know is whether or not the quantity of any given item is enough to "cover" any other related item. Mathemetically it's (total quant of all related items)-(total quant of this item) If that number is bigger than zero, we're cool. Now this looks like something you could do with a SUMPRODUCT, right? Well no such luck (I think). As you can see, the database is lazy and doesn't put in default values for items per pack if it's only 1. So I need to put an ISNULL(quantity,1) in there. Is there a way to do this in SUMPRODUCT? Then I looked at using just SUM for this, but I can't figure out how this works. In the SUMPRODUCT help there is a very confusing statement; "The preceding example returns the same result as the formula SUM(A2:B4*C2:D4) entered as an array" So I tried a few experiments with SUM, but I can't figure out what it's doing. I put two columns of four numbers in a sheet and used =SUM(A1:A4*B1:B4), and the result was simply the value of A*B, it didn't sum up over the rows. Am I missing something obvious here? The other problem is the one that really has me stumped. The grouping for summing up is basically if my underid is in either the first OR second column. That is, the total is anything that has my underid as its underid, or anything that has my underid as its ID. The good news is that it's basically an ISNULL(underid, ID), because we want to use ID only if Anyone have some suggestions? BTW, the sheet is 1500 rows long, and about 50 columns wide, so major surgery in terms of adding new related columns might not be easy. Maury |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum of "everything but me".
Maury
I changed the headings slightly my layout is: Parts related_to packages items/pack Stock Shortf/Over Girders 10 10 0 Bolts Girders 2 10 20 10 Nuts Girders 1 10 10 0 Hoses 5 5 0 Nozzles Hoses 2 2 -3 Formula in E1; =IF(ISBLANK(D2),C2,C2*D2) Formula in F1: =SUMPRODUCT(--($A$2:$A$6=A2),($B$2:$B$6"")*($E$2:$E$6))-SUMPRODUCT(--($A$2:$A$6=B2)*($E$2:$E$6)) Hopes this helps Peter "Maury Markowitz" wrote: I have a sheet of inventory broken down by location. Some items are related to other items, for instance, there are nuts and bolts that are needed to fasten girders. We maintain this relationship through the primary IDs in our associated database; if an item in question has a "underid", then it is related to some other item. For instance: thing related to packages items/pack Girders 10 Bolts Girders 1 10 Nuts Girders 1 10 Hoses 5 Nozzles Hoses 2 The formula I'm trying to write is to make sure that I don't have a shortfall of any one item at any one location. In the example above, we have enough nuts and bolts for the girders, but we don't have enough nozzles for the hoses. So what I need to know is whether or not the quantity of any given item is enough to "cover" any other related item. Mathemetically it's (total quant of all related items)-(total quant of this item) If that number is bigger than zero, we're cool. Now this looks like something you could do with a SUMPRODUCT, right? Well no such luck (I think). As you can see, the database is lazy and doesn't put in default values for items per pack if it's only 1. So I need to put an ISNULL(quantity,1) in there. Is there a way to do this in SUMPRODUCT? Then I looked at using just SUM for this, but I can't figure out how this works. In the SUMPRODUCT help there is a very confusing statement; "The preceding example returns the same result as the formula SUM(A2:B4*C2:D4) entered as an array" So I tried a few experiments with SUM, but I can't figure out what it's doing. I put two columns of four numbers in a sheet and used =SUM(A1:A4*B1:B4), and the result was simply the value of A*B, it didn't sum up over the rows. Am I missing something obvious here? The other problem is the one that really has me stumped. The grouping for summing up is basically if my underid is in either the first OR second column. That is, the total is anything that has my underid as its underid, or anything that has my underid as its ID. The good news is that it's basically an ISNULL(underid, ID), because we want to use ID only if Anyone have some suggestions? BTW, the sheet is 1500 rows long, and about 50 columns wide, so major surgery in terms of adding new related columns might not be easy. Maury |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum of "everything but me".
"Jim" wrote:
Lots of questions there - but I'll take a stab at some of them. Yes, and thanks! First, with your data in A1:D6 and a list of your unique things in K2:K6, the following formula in L2 (and copied to L3:L6) will give you the correct count of the unique things: =SUMPRODUCT(--($A$2:$A$6=$K2),($C$2:$C$6),($D$2:$D$6))+SUMPRODUC T(--($A$2:$A$6=$K2),--($D$2:$D$6=0),($C$2:$C$6)) I am going to start playing with this now! I do have two questions though. First off, what is the "--" syntax? I have never used this before but I keep seeing it in examples here in for the forums, so it seems like it's something I should know about. Secondly, what happens in the case of null columns? For this data set (which is handed on-high from the database) the key count is total items = (quantity times items/package). However, just due to the way the database is organized, items that have 1 item/package are listed as having "null" items/package. Is there a way to feed in some sort of ISNULL(items/package, 1)? I could write all of this in a few lines of VBA, but I do that so much my knowledge of formula crafting is seriously lacking! Second, your SUM formula is an array function and must be committed (entered) with Ctrl-Shift-Enter to produce {sum(____:_____)}. if you just hit enter, you will not have an array function. Ohhh, weird. Ok, one for the memory banks. I'm not sure how you want to count the "related to" items. Post your expected answers and someone will be able to help you out. Oh geez, I posted inputs and no outputs! Sorry! Here's what I'm looking for thing related to packages items/pack surplus/shortfall Girders 10 <- nothing, this is a "root product" Bolts Girders 1 10 -10 <- 10 girders - 10 nuts - 10 bolts Nuts Girders 1 10 -10 <- 10 girders - 10 nuts - 10 bolts Hoses 5 <- nothing, this is a "root product" Nozzles Hoses 2 3 <- 5 hoses - 2 nozzles Now all I need to do is put a conditional formatter on the surplus/shortfall column, items smaller than zero are green and ones greater than zero are red. Or I could invert the numbers to make suplusses positive... :-) Maury |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum of "everything but me".
"Billy Liddel" wrote:
Parts related_to packages items/pack Stock Shortf/Over Girders 10 10 0 Bolts Girders 2 10 20 10 Nuts Girders 1 10 10 0 Hoses 5 5 0 Nozzles Hoses 2 2 -3 Formula in E1; =IF(ISBLANK(D2),C2,C2*D2) Formula in F1: =SUMPRODUCT(--($A$2:$A$6=A2),($B$2:$B$6"")*($E$2:$E$6))-SUMPRODUCT(--($A$2:$A$6=B2)*($E$2:$E$6)) Hopes this helps Where should I send my first born?! Thanks! Maury |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum of "everything but me".
Maury
Thanks for the feedback - you're welcome Peter "Maury Markowitz" wrote: "Billy Liddel" wrote: Parts related_to packages items/pack Stock Shortf/Over Girders 10 10 0 Bolts Girders 2 10 20 10 Nuts Girders 1 10 10 0 Hoses 5 5 0 Nozzles Hoses 2 2 -3 Formula in E1; =IF(ISBLANK(D2),C2,C2*D2) Formula in F1: =SUMPRODUCT(--($A$2:$A$6=A2),($B$2:$B$6"")*($E$2:$E$6))-SUMPRODUCT(--($A$2:$A$6=B2)*($E$2:$E$6)) Hopes this helps Where should I send my first born?! Thanks! Maury |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
Complex if test program possible? If "value" "value", paste "value" in another cell? | Excel Discussion (Misc queries) | |||
Insert "-" in text "1234567890" to have a output like this"123-456-7890" | Excel Discussion (Misc queries) |