Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Autofill counting up Columns and Rows!
Hi Having some trouble with autofilling a formula that looks like this:
=SUM(A9550:A10806) and i want it to autofill down like this by draging the handle down: =SUM(A9550:A10806) =SUM(B9551:B10807) =SUM(C9552:C10808) that is both column AND row value should increase! -- ------------------ ------------ Power Student With Power in My Power Boots |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Autofill counting up Columns and Rows!
You're probably going to need OFFSET().
If your 1st formula is in row 1, then use =SUM(OFFSET(A$9550,ROW()-1,ROW()-1):OFFSET(A$10806,ROW()-1,ROW()-1)), and copy down. Another that should work in the same situation would be =SUM(OFFSET(A9550,0,ROW()-1):OFFSET(A10806,0,ROW()-1)) and copy that down. -- David Biddulph "fantomet" wrote in message ... Hi Having some trouble with autofilling a formula that looks like this: =SUM(A9550:A10806) and i want it to autofill down like this by draging the handle down: =SUM(A9550:A10806) =SUM(B9551:B10807) =SUM(C9552:C10808) that is both column AND row value should increase! -- ------------------ ------------ Power Student With Power in My Power Boots |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Autofill counting up Columns and Rows!
Excel doesnt want do do it, it complaines about the frase: "..A9550,0,ROW.."
same with the other example (same part of formula), but my formula is in J258 if that changes anything though ive tried it in a new worsheet in cell A1 as well. Is this function only for newest excel versions is it suppose to work on excel 2002? Shouldent it be cols rows instead of rows rows!? -- Power Student With Power in My Power Boots "David Biddulph" wrote: You're probably going to need OFFSET(). If your 1st formula is in row 1, then use =SUM(OFFSET(A$9550,ROW()-1,ROW()-1):OFFSET(A$10806,ROW()-1,ROW()-1)), and copy down. Another that should work in the same situation would be =SUM(OFFSET(A9550,0,ROW()-1):OFFSET(A10806,0,ROW()-1)) and copy that down. -- David Biddulph "fantomet" wrote in message ... Hi Having some trouble with autofilling a formula that looks like this: =SUM(A9550:A10806) and i want it to autofill down like this by draging the handle down: =SUM(A9550:A10806) =SUM(B9551:B10807) =SUM(C9552:C10808) that is both column AND row value should increase! -- ------------------ ------------ Power Student With Power in My Power Boots |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Autofill counting up Columns and Rows!
Could someone please help me! This problem is eating my braines, when i type
in the above formula excel complaines that something is wrong, what criterias must be fullfilled for the formula to work!? /fantomet -- Power Student With Power in My Power Boots "fantomet" wrote: Excel doesnt want do do it, it complaines about the frase: "..A9550,0,ROW.." same with the other example (same part of formula), but my formula is in J258 if that changes anything though ive tried it in a new worsheet in cell A1 as well. Is this function only for newest excel versions is it suppose to work on excel 2002? Shouldent it be cols rows instead of rows rows!? -- Power Student With Power in My Power Boots "David Biddulph" wrote: You're probably going to need OFFSET(). If your 1st formula is in row 1, then use =SUM(OFFSET(A$9550,ROW()-1,ROW()-1):OFFSET(A$10806,ROW()-1,ROW()-1)), and copy down. Another that should work in the same situation would be =SUM(OFFSET(A9550,0,ROW()-1):OFFSET(A10806,0,ROW()-1)) and copy that down. -- David Biddulph "fantomet" wrote in message ... Hi Having some trouble with autofilling a formula that looks like this: =SUM(A9550:A10806) and i want it to autofill down like this by draging the handle down: =SUM(A9550:A10806) =SUM(B9551:B10807) =SUM(C9552:C10808) that is both column AND row value should increase! -- ------------------ ------------ Power Student With Power in My Power Boots |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Autofill counting up Columns and Rows!
If your formula is in row 258, you'll need
=SUM(OFFSET(A$9550,ROW()-258,ROW()-258):OFFSET(A$10806,ROW()-258,ROW()-258)) or =SUM(OFFSET(A9550,0,ROW()-258):OFFSET(A10806,0,ROW()-258)) I'm using Excel 2003, but I'm sure that all the functions in the formula will work with earlier versions. It's always easier for contributors to the group if you give some detail of the problem. You say: "Excel doesnt want do do it, it complaines about the frase ..." , but it's more likely that folk will be able to help you if you tell us what error message Excel gives. You say: "Shouldent it be cols rows instead of rows rows!?" No it shouldn't, because you wanted to autofill your formula downwards, so the row is changing and the column isn't. You want the formula to point to cells which increase in both row and column, so you need to offset the column count by an extra 1 when the row in which your formula is sitting increases by 1. -- David Biddulph "fantomet" wrote in message ... Excel doesnt want do do it, it complaines about the frase: "..A9550,0,ROW.." same with the other example (same part of formula), but my formula is in J258 if that changes anything though ive tried it in a new worsheet in cell A1 as well. Is this function only for newest excel versions is it suppose to work on excel 2002? Shouldent it be cols rows instead of rows rows!? -- Power Student With Power in My Power Boots "David Biddulph" wrote: You're probably going to need OFFSET(). If your 1st formula is in row 1, then use =SUM(OFFSET(A$9550,ROW()-1,ROW()-1):OFFSET(A$10806,ROW()-1,ROW()-1)), and copy down. Another that should work in the same situation would be =SUM(OFFSET(A9550,0,ROW()-1):OFFSET(A10806,0,ROW()-1)) and copy that down. -- David Biddulph "fantomet" wrote in message ... Hi Having some trouble with autofilling a formula that looks like this: =SUM(A9550:A10806) and i want it to autofill down like this by draging the handle down: =SUM(A9550:A10806) =SUM(B9551:B10807) =SUM(C9552:C10808) that is both column AND row value should increase! -- ------------------ ------------ Power Student With Power in My Power Boots |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Autofill counting up Columns and Rows!
Excel doesn't just "complain that something is wrong". It gives an error
message. If you tell us what the error message is, we might be able to help you. Otherwise, you are reliant on the clairvoyant talents of the contributors to the group. -- David Biddulph "fantomet" wrote in message ... Could someone please help me! This problem is eating my braines, when i type in the above formula excel complaines that something is wrong, what criterias must be fullfilled for the formula to work!? /fantomet -- Power Student With Power in My Power Boots "fantomet" wrote: Excel doesnt want do do it, it complaines about the frase: "..A9550,0,ROW.." same with the other example (same part of formula), but my formula is in J258 if that changes anything though ive tried it in a new worsheet in cell A1 as well. Is this function only for newest excel versions is it suppose to work on excel 2002? Shouldent it be cols rows instead of rows rows!? -- Power Student With Power in My Power Boots "David Biddulph" wrote: You're probably going to need OFFSET(). If your 1st formula is in row 1, then use =SUM(OFFSET(A$9550,ROW()-1,ROW()-1):OFFSET(A$10806,ROW()-1,ROW()-1)), and copy down. Another that should work in the same situation would be =SUM(OFFSET(A9550,0,ROW()-1):OFFSET(A10806,0,ROW()-1)) and copy that down. -- David Biddulph "fantomet" wrote in message ... Hi Having some trouble with autofilling a formula that looks like this: =SUM(A9550:A10806) and i want it to autofill down like this by draging the handle down: =SUM(A9550:A10806) =SUM(B9551:B10807) =SUM(C9552:C10808) that is both column AND row value should increase! -- ------------------ ------------ Power Student With Power in My Power Boots |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cutting and pasting from rows into columns and visa versa. | New Users to Excel | |||
Counting rows based on multiple columns | Excel Worksheet Functions | |||
autofill information from rows to columns without using transpose | Excel Discussion (Misc queries) | |||
Counting rows of blanks across certain columns | New Users to Excel | |||
Counting rows with 3 columns | Excel Worksheet Functions |