| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct I can't find an answer for!
An example of a formula I have is: =SUMPRODUCT($E$23:$E$29*F23:F29)/$E$30. I
copy/fill the formula to the right, which works fine; however, I then want to copy the formula down to the next "grouping" of cell data (locations), the number of cells calculated changing with each grouping, e.g., the above has a 7 cell range but the next group might have 3 and then the next might have 12, etc., etc., for many, many groupings, so it ends up being a lot of manual changing of formulas. Any help is greatly apprecaited. Thanks! -- Marv Lusk |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct I can't find an answer for!
Marv,
Is there a column of values that determines the groupings? HTH, Bernie MS Excel MVP "MarvInBoise" wrote in message ... An example of a formula I have is: =SUMPRODUCT($E$23:$E$29*F23:F29)/$E$30. I copy/fill the formula to the right, which works fine; however, I then want to copy the formula down to the next "grouping" of cell data (locations), the number of cells calculated changing with each grouping, e.g., the above has a 7 cell range but the next group might have 3 and then the next might have 12, etc., etc., for many, many groupings, so it ends up being a lot of manual changing of formulas. Any help is greatly apprecaited. Thanks! -- Marv Lusk |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct I can't find an answer for!
It's always column E that has the absolute reference, if that's your
question; and thanks for the response! -- Marv Lusk Boise Corporation "Bernie Deitrick" wrote: Marv, Is there a column of values that determines the groupings? HTH, Bernie MS Excel MVP "MarvInBoise" wrote in message ... An example of a formula I have is: =SUMPRODUCT($E$23:$E$29*F23:F29)/$E$30. I copy/fill the formula to the right, which works fine; however, I then want to copy the formula down to the next "grouping" of cell data (locations), the number of cells calculated changing with each grouping, e.g., the above has a 7 cell range but the next group might have 3 and then the next might have 12, etc., etc., for many, many groupings, so it ends up being a lot of manual changing of formulas. Any help is greatly apprecaited. Thanks! -- Marv Lusk |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct I can't find an answer for!
Marv,
No, my question is how, when you "want to copy the formula down to the next "grouping" of cell data (locations), the number of cells calculated changing with each grouping" How do you tell what is a group - blank lines, another column with a value like "Group 1" "Group 2", ESP perhaps? HTH, Bernie MS Excel MVP "MarvInBoise" wrote in message ... It's always column E that has the absolute reference, if that's your question; and thanks for the response! -- Marv Lusk Boise Corporation "Bernie Deitrick" wrote: Marv, Is there a column of values that determines the groupings? HTH, Bernie MS Excel MVP "MarvInBoise" wrote in message ... An example of a formula I have is: =SUMPRODUCT($E$23:$E$29*F23:F29)/$E$30. I copy/fill the formula to the right, which works fine; however, I then want to copy the formula down to the next "grouping" of cell data (locations), the number of cells calculated changing with each grouping, e.g., the above has a 7 cell range but the next group might have 3 and then the next might have 12, etc., etc., for many, many groupings, so it ends up being a lot of manual changing of formulas. Any help is greatly apprecaited. Thanks! -- Marv Lusk |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct I can't find an answer for!
Ah, here's a partial "grouping." So the bottom line before the blank line is
where the formula is, e.g., in Fox River, it is the 503 figure. Fox River Paper Urbana, OH 50 17,850 500 Fox River Paper Ripon, CA 90 32,130 488 Fox River Paper Appleton, WI 61 21,777 527 Fox River Paper 71,757 503 Fraser Papers Gorham, NH 140 49,980 281 Fraser Papers Madawaska, ME 690 246,330 282 Fraser Papers 296,310 282 French Paper Niles, MI 54 19,278 486 Georgia-Pacific Port Hudson, LA 0 0 0 Georgia-Pacific Camas, WA 0 0 0 Georgia-Pacific Crossett, AR 0 0 0 Georgia-Pacific Wauna, OR 0 0 0 Georgia-Pacific 0 0 0 -- Marv Lusk Boise Corporation "Bernie Deitrick" wrote: Marv, No, my question is how, when you "want to copy the formula down to the next "grouping" of cell data (locations), the number of cells calculated changing with each grouping" How do you tell what is a group - blank lines, another column with a value like "Group 1" "Group 2", ESP perhaps? HTH, Bernie MS Excel MVP "MarvInBoise" wrote in message ... It's always column E that has the absolute reference, if that's your question; and thanks for the response! -- Marv Lusk Boise Corporation "Bernie Deitrick" wrote: Marv, Is there a column of values that determines the groupings? HTH, Bernie MS Excel MVP "MarvInBoise" wrote in message ... An example of a formula I have is: =SUMPRODUCT($E$23:$E$29*F23:F29)/$E$30. I copy/fill the formula to the right, which works fine; however, I then want to copy the formula down to the next "grouping" of cell data (locations), the number of cells calculated changing with each grouping, e.g., the above has a 7 cell range but the next group might have 3 and then the next might have 12, etc., etc., for many, many groupings, so it ends up being a lot of manual changing of formulas. Any help is greatly apprecaited. Thanks! -- Marv Lusk |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct I can't find an answer for!
Perhaps, and this is a big perhaps:
=SUMPRODUCT($E$2:$E$200*$F$2:$F$200*(LEFT($C$2:$C$ 200,LEN($C30))=$C30))/$E30 This is based on entries in column C at least having the same beginning string as the string on row 30, and the formula being entered on row 30. Then it can be copied elsewhere. The table can be Column C Column D...... Fox River Paper Urbana, OH 50 17,850 500 Fox River Paper Ripon, CA 90 32,130 488 Fox River Paper Appleton, WI 61 21,777 527 Fox River Paper 71,757 503 Or it can be Column C Column D...... Fox River Paper Urbana, OH 50 17,850 500 Fox River Paper Ripon, CA 90 32,130 488 Fox River Paper Appleton, WI 61 21,777 527 Fox River Paper 71,757 503 HTH, Bernie MS Excel MVP "MarvInBoise" wrote in message ... Ah, here's a partial "grouping." So the bottom line before the blank line is where the formula is, e.g., in Fox River, it is the 503 figure. Fox River Paper Urbana, OH 50 17,850 500 Fox River Paper Ripon, CA 90 32,130 488 Fox River Paper Appleton, WI 61 21,777 527 Fox River Paper 71,757 503 Fraser Papers Gorham, NH 140 49,980 281 Fraser Papers Madawaska, ME 690 246,330 282 Fraser Papers 296,310 282 French Paper Niles, MI 54 19,278 486 Georgia-Pacific Port Hudson, LA 0 0 0 Georgia-Pacific Camas, WA 0 0 0 Georgia-Pacific Crossett, AR 0 0 0 Georgia-Pacific Wauna, OR 0 0 0 Georgia-Pacific 0 0 0 -- Marv Lusk Boise Corporation "Bernie Deitrick" wrote: Marv, No, my question is how, when you "want to copy the formula down to the next "grouping" of cell data (locations), the number of cells calculated changing with each grouping" How do you tell what is a group - blank lines, another column with a value like "Group 1" "Group 2", ESP perhaps? HTH, Bernie MS Excel MVP "MarvInBoise" wrote in message ... It's always column E that has the absolute reference, if that's your question; and thanks for the response! -- Marv Lusk Boise Corporation "Bernie Deitrick" wrote: Marv, Is there a column of values that determines the groupings? HTH, Bernie MS Excel MVP "MarvInBoise" wrote in message ... An example of a formula I have is: =SUMPRODUCT($E$23:$E$29*F23:F29)/$E$30. I copy/fill the formula to the right, which works fine; however, I then want to copy the formula down to the next "grouping" of cell data (locations), the number of cells calculated changing with each grouping, e.g., the above has a 7 cell range but the next group might have 3 and then the next might have 12, etc., etc., for many, many groupings, so it ends up being a lot of manual changing of formulas. Any help is greatly apprecaited. Thanks! -- Marv Lusk |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct I can't find an answer for!
Thanks Bernie, I'll take a look and give it a shot,
-- Marv Lusk Boise Corporation "Bernie Deitrick" wrote: Perhaps, and this is a big perhaps: =SUMPRODUCT($E$2:$E$200*$F$2:$F$200*(LEFT($C$2:$C$ 200,LEN($C30))=$C30))/$E30 This is based on entries in column C at least having the same beginning string as the string on row 30, and the formula being entered on row 30. Then it can be copied elsewhere. The table can be Column C Column D...... Fox River Paper Urbana, OH 50 17,850 500 Fox River Paper Ripon, CA 90 32,130 488 Fox River Paper Appleton, WI 61 21,777 527 Fox River Paper 71,757 503 Or it can be Column C Column D...... Fox River Paper Urbana, OH 50 17,850 500 Fox River Paper Ripon, CA 90 32,130 488 Fox River Paper Appleton, WI 61 21,777 527 Fox River Paper 71,757 503 HTH, Bernie MS Excel MVP "MarvInBoise" wrote in message ... Ah, here's a partial "grouping." So the bottom line before the blank line is where the formula is, e.g., in Fox River, it is the 503 figure. Fox River Paper Urbana, OH 50 17,850 500 Fox River Paper Ripon, CA 90 32,130 488 Fox River Paper Appleton, WI 61 21,777 527 Fox River Paper 71,757 503 Fraser Papers Gorham, NH 140 49,980 281 Fraser Papers Madawaska, ME 690 246,330 282 Fraser Papers 296,310 282 French Paper Niles, MI 54 19,278 486 Georgia-Pacific Port Hudson, LA 0 0 0 Georgia-Pacific Camas, WA 0 0 0 Georgia-Pacific Crossett, AR 0 0 0 Georgia-Pacific Wauna, OR 0 0 0 Georgia-Pacific 0 0 0 -- Marv Lusk Boise Corporation "Bernie Deitrick" wrote: Marv, No, my question is how, when you "want to copy the formula down to the next "grouping" of cell data (locations), the number of cells calculated changing with each grouping" How do you tell what is a group - blank lines, another column with a value like "Group 1" "Group 2", ESP perhaps? HTH, Bernie MS Excel MVP "MarvInBoise" wrote in message ... It's always column E that has the absolute reference, if that's your question; and thanks for the response! -- Marv Lusk Boise Corporation "Bernie Deitrick" wrote: Marv, Is there a column of values that determines the groupings? HTH, Bernie MS Excel MVP "MarvInBoise" wrote in message ... An example of a formula I have is: =SUMPRODUCT($E$23:$E$29*F23:F29)/$E$30. I copy/fill the formula to the right, which works fine; however, I then want to copy the formula down to the next "grouping" of cell data (locations), the number of cells calculated changing with each grouping, e.g., the above has a 7 cell range but the next group might have 3 and then the next might have 12, etc., etc., for many, many groupings, so it ends up being a lot of manual changing of formulas. Any help is greatly apprecaited. Thanks! -- Marv Lusk |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Is sumproduct the answer? | Excel Discussion (Misc queries) | |||
| Hide Answer in Excel | Excel Worksheet Functions | |||
| find | Excel Worksheet Functions | |||
| Find Function | Excel Discussion (Misc queries) | |||
| Sumproduct Will Not Find Date | Excel Worksheet Functions | |||
Linear Mode
