Previous Topic Next Topic

Need to sum Coumn F only when B & C identical

Solved/Closed
mazeracer - Jan 13, 2010 at 12:26 PM
tompols Posts 1273 Registration date Wednesday July 28, 2004 Status Contributor Last seen November 25, 2013 - Jan 14, 2010 at 04:30 PM
Hello,
I am trying to set up a formula that will allow me to get the average of values in F, but only for the rows where both Coumn B & C are identical.

I have Column A set to be a conditional "start of like data" and use an "x" in the first row of identical samples. So, I know I can do an =if(a5="x", ...,"") to leave the value blank if it's not the first sample. However, the "..." portion of the fourmula to ONLY get the average for a single set of conditions is giving me fits.

Basically, I have Column B set to be Sample ID (15 mil cast, 20 mil cast, etc), Column C is sintering conditions (1300C/1h, 1400C/1h, etc). I have column F already set up to give each row's shrinkage after sintering, and just need to be able to get the average of these - but the catch is, the number of samples per run can change. Any ideas or suggestions on how to do this. Also, Excel 2003 is in use.

11 responses

Answer 1 / 11
tompols Posts 1273 Registration date Wednesday July 28, 2004 Status Contributor Last seen November 25, 2013 28
Jan 13, 2010 at 12:47 PM
Hi,
I am trying to set up a formula that will allow me to get the average of values in F, but only for the rows where both Coumn B & C are identical => this would be :
=sumproduct(((B1:B10)=(C1:C10))*(F1:F10))/COUNTA(F1:F10)
please send a file(or post some sample data and expected result) if you need more help as i'm not getting everything you said, my english is quite poor ("but the catch is, the number of samples per run can change" => where's that number of samples ? and also i have no idea what "sintering" means )
0
mazeracer
Jan 13, 2010 at 01:15 PM
Thanks - I had actually just figured that out, except for the counta portion - I had it being done in 2 columns using sumproduct to get a count as well, since there are other conditions that fill column F. I used the following:

Calculate the sum of column F by:
=IF(A5="x",SUMPRODUCT((B5:B50=B5)*(C5:C50=C5)*(F5:F50)),"")

Then to get the count of rows that are the same in B & C by:
=IF(A5="x",SUMPRODUCT((B5:B50=B5)*(C5:C50=C5)),"")

These can be hidden columns, and then just have a simple formula in the next to divide the sum by the count.

Now, the harder question is - can I do something similar to get the Standard Dev (STDEV) of the same vaules? Again, only when B & C are identical - need STDEV of column F.

Here's the data I am currently working with as I test this (first row is coumn headings):
B C D E F
Sample ID: Sintering Conditions: Starting diameter(mm): Measured diameter(mm): % Original Size:
15 Mil Cast 1375/1 1.5 1.153 23.13333%
15 Mil Cast 1375/1 1.5 1.154 23.06667%
15 Mil Cast 1375/1 1.5 1.155 23.00000%
15 Mil Cast 1375/1 1.5 1.16 22.66667%

30 Mil Cast 1350/0.5 1.5 1.0 33.33333%
30 Mil Cast 1350/0.5 1.5 1.1 26.66667%
30 Mil Cast 1350/0.5 1.5 1.3 13.33333%
30 Mil Cast 1350/0.5 1.5 1.133 24.46667%
0
Answer 2 / 11
tompols Posts 1273 Registration date Wednesday July 28, 2004 Status Contributor Last seen November 25, 2013 28
Jan 13, 2010 at 01:37 PM
Hi,
Ok then you need to create a matrix to pass to STDEV :
=((B1:B10)=(C1:C10))*(F1:F10) will return the needed matrix (column F value when colum B equals colum C)
then simply apply STDEV to this matrix : 
=STDEV(((B1:B10)=(C1:C10))*(F1:F10))
validate formula using CTRL+SHIFT+ENTER
EDIT : sorry, just realized this doesn't work as the matrix gives 0 as a value when B is different to C....
0
Answer 3 / 11
tompols Posts 1273 Registration date Wednesday July 28, 2004 Status Contributor Last seen November 25, 2013 28
Jan 13, 2010 at 01:53 PM
Hi,
did not find any function to help you yet, will keep trying...
would some vba code fit ?
0
mazeracer
Jan 13, 2010 at 01:59 PM
Thanks Tompols, as you noticed I need to get the STDEV for all rows where column B = "15 Mil Cast" and column C = "1375/1" I should have copied more of the table for you to to show that I also have data that would be column B = "15 Mil Cast" and column C = "1400/1"

VBA as a last resort would be OK, but if there's a way to do this in cell it would be desireable as I am passing the sheet along to my technician who will be entering the data. I'm trying to automate the data analysis portion for him.

Thanks again for your help. I have to go to a meeting for the next couple hours, but will check back when I return to my office.
0
mazeracer
Jan 14, 2010 at 10:26 AM
Acutally, if the VBA could be written such that it could be assigned to a button that would be included on the sheet and not care that the workbook gets re-named all the time, then that would be fine. My worry is, we keep each individual composition in a separate workbook - starting from the same read-only file for each new material, but updating differnent furnace temperatures and casting thicknesses (columns B & C) for a given material from a saved copy of the original workbook. So, if the file can change names and not break the links assigned to the macro assigned to the button that should work just fine.
0
Answer 4 / 11
tompols Posts 1273 Registration date Wednesday July 28, 2004 Status Contributor Last seen November 25, 2013 28
Jan 14, 2010 at 12:01 PM
Hi,
sorry for late reply but i was sleeping when you wrote that (i'm in Paris, FR). I suggest a vba function that you could use in a speadsheet as other formulas...It would be stored in a module in the workbook, but using this workbook as a read only template and then "saving as new workboook" would copy the module as well.... Is this ok for you ?
0
mazeracer
Jan 14, 2010 at 12:42 PM
No problem, had guessed from your signature that you were in France somewhere. I'll be visiting Paris (hopefully) next Fall - Sister-in-law is stationed in Germany right now and loves Paris (fluent in French language too as a bonus).

I was thinking along the same line as you for the module. I just didn't know if the code to the button would be workbook name dependant or not. If not, then I think that should be fine. Thanks for the help!
0

Didn't find the answer you are looking for?

Ask a question
Answer 5 / 11
tompols Posts 1273 Registration date Wednesday July 28, 2004 Status Contributor Last seen November 25, 2013 28
Jan 14, 2010 at 01:09 PM
Ok, then try this (copy the code in a module) : 
Function stdevBequalsC(plage As Range)

Dim c As Range
Dim chaine As String
Dim cpt As Integer

For Each c In plage
    If Range("B" & c.Row) = Range("C" & c.Row) Then
        cpt = cpt + 1
        If cpt = 1 Then
            chaine = "F" & c.Row
            cont = c.Row
        Else
            If cont = c.Row - 1 Then
                chaine = chaine & ":F" & c.Row
            Else
                chaine = chaine & ",F" & c.Row
            End If
        End If
    End If
    
Next c

stdevBequalsC = WorksheetFunction.StDev(Range(chaine))

End Function

then simply type =stdevBequalsC(F1:F12) in a cell to calculate stdev when B=C for range F1:F12...will only work when formula is on the same worksheet as data, reference to another worksheet is not supported yet(i can still adapt it if needed)...
0
mazeracer
Jan 14, 2010 at 01:32 PM
Not quite what I need, but I think if it could be adapted this will work very well.

What I need is to get the STDEV for the range of rows with identical values for Column B that also have identical values in column C.

For example, if Column B is "15 Mil cast" in rows 1-5, and Column C is "1375C/1" in rows 1-5, but Column B is "15 Mil cast" in rows 7-11 and Column C is "1400C/1", then I want STDEV for rows 1-5 of data in column F, to show in I1, and then in I7 I want the STDEV for rows 7-11 only. Does that make sense? If not, is there a way to get a version of the file to you with the current manually sorted data so you can see it better?
0
Answer 6 / 11
tompols Posts 1273 Registration date Wednesday July 28, 2004 Status Contributor Last seen November 25, 2013 28
Jan 14, 2010 at 02:04 PM
I think i understand what you need but it's always better with an example : you can upload the file here www.cjoint.com and post the link generated.....
In this case, i would probably insert a new column with formula =B1&C1 and use excel functionality "subtotals" (data menu) with breakdown based on this new column.....
0
mazeracer
Jan 14, 2010 at 02:29 PM
Thanks,

I have uploaded a file called test.xls that has data showing what I am trying to do. If you look at the formula in column H, that is what I am trying to automate. Column G is where I have the automated formula for creating the average for identical sample and sintering conditions (Columns B & C). The link created is:
http://cjoint.com/data/bovwEMIMzl.htm

Please let me know if you have any other questions, and again THANK YOU for all your help!
0
Answer 7 / 11
tompols Posts 1273 Registration date Wednesday July 28, 2004 Status Contributor Last seen November 25, 2013 28
Jan 14, 2010 at 02:43 PM
Q: do you always have blank lines between datasets ? do you insert it manually ?
0
mazeracer
Jan 14, 2010 at 02:48 PM
Yes, I always manually leave a blank row between data sets. I also always use the "x" in column A to note the start of a new data set.
0
Answer 8 / 11
tompols Posts 1273 Registration date Wednesday July 28, 2004 Status Contributor Last seen November 25, 2013 28
Jan 14, 2010 at 02:59 PM
Ok, have a look at this and tell me if it is OK : https://www.cjoint.com/?bov5Bvv5AG
I just deleted blank rows, added a column "test column" (=B1&C1) and used subtotals functionnality (2 times, details on sheet 3)...easiest way, no code, no complex formulas....
0
mazeracer
Jan 14, 2010 at 03:36 PM
That gives the correct values, however, I'm not understanding how you are getting it to insert these at the proper point. Are you having to manually having to put them in in row 9 & 10, and then again in row 20 & 21? If so, that isn't really what I'm after as the number of data entries for each condition can and will fluctuate. (4 entries for "15 mil cast & 1375/1", then 9 entries for "30 mil cast & 1350/0.5 as an example). What I am really hoping to be able to do is like what I have on my original sheet in column H, where based on me entering the x in columnA, It will match all the data that has the combined set of info required, regardless how many entries are needed - and without having to enter the formual each time. I'm not seeing how I could use the subtotal function to do this, is there something I am missing that would let me do this? That is why I have the data in a separate column at the end, and only have the value show if there's an x in column A. Not sure if this is making sense or not. Let me know.
0
Answer 9 / 11
tompols Posts 1273 Registration date Wednesday July 28, 2004 Status Contributor Last seen November 25, 2013 28
Jan 14, 2010 at 03:44 PM
Ok, you just missed the subtotals functionnality not function (Data menu, Subtotals command : http://office.microsoft.com/en-us/excel/HA011097981033.aspx). I put screenshots of subtotals parameters in sheet3...
let me know if it still doesn't fit your requirements
0
mazeracer
Jan 14, 2010 at 03:59 PM
Ah, yes, I missed the part about using it from the Data menu. That does work, but I still would prefer to find a way to have it in the row with the data if it were less problematic. But, the only way I have managed to do it until today was to go through a very lenghty formula that compares cells for exact data (have actually had to use 2 cells to do this in order to get through all the cells since it was more than 7 levels deep of IF statements combined with EXACT and AND functions - not fun).

I will work with this a bit more and see if I can get my summary table to bring in the data from this page in my actual workbook.
0
Answer 10 / 11
tompols Posts 1273 Registration date Wednesday July 28, 2004 Status Contributor Last seen November 25, 2013 28
Jan 14, 2010 at 04:06 PM
"have actually had to use 2 cells to do this in order to get through all the cells since it was more than 7 levels deep of IF statements combined with EXACT and AND functions - not fun" => that's why i'm proposing this solution, keep me informed... ;)
0
mazeracer
Jan 14, 2010 at 04:22 PM
It may be a couple days, as I have a report I have to get out and laboratory work to catch up on - but I think I'll be able to make it work. Will require re-arranging things on other pages to use the same method of naming and calculation, but I think it may be easier in the long run! Thanks again for the help, I've learned several new things from our exchanges.

Also - I just realized I never told you what "sintering" means - it is the processing term for taking a powder up in temperature and causing the individual grains of powder to fuse together to creat a solid piece. Similar to baking a cake.
0
Answer 11 / 11
tompols Posts 1273 Registration date Wednesday July 28, 2004 Status Contributor Last seen November 25, 2013 28
Jan 14, 2010 at 04:30 PM
now i know what sintering means, l learned from our exchanges as well (and it made me speak/write in english, which is good for me aswell).... :)
0

Similar discussions

Instagram Account Hacked!
artistfromNW3 -
Domme1 -

39 responses
Excel Sum Greater than but Less Than
Ian -
Alli -

4 responses
Confirm your identity with phone or computer
Sajibdas217@gmail.com -
ElenaKM -

1 response
Selfie video verification upload error
Soslilfrost -
Muaidxd -

18 responses
confirm your identity problem
Zn -
HelpiOS -

1 response