| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question about a formula
I trying to fig out what the formula is doing/
FormulaR1C1 = "=SUMIF(TOEPIEXP!C[2],1,TOEPIEXP!C[40])" TOEPIEXP!C i know that TOEPIEXP is the worksheet what is the C? [2],1,TOEPIEXP!C[40])" What is the [2] ? The 1 is the criteria. TOEPIEXP is the work sheet. What is C? what is 40? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200612/1 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Question about a formula
jln
The [C]2 is R1C1 notation, the C is for 'Column' and the 2 is Column 2 so B and as it has no '[R]' refers to the whole column. (Lookup Range) The 1 is the criteria and the second [C]40 is the range that is summed Column 40 -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "jln via OfficeKB.com" <u25956@uwe wrote in message news:6afe660e3e8b6@uwe... I trying to fig out what the formula is doing/ FormulaR1C1 = "=SUMIF(TOEPIEXP!C[2],1,TOEPIEXP!C[40])" TOEPIEXP!C i know that TOEPIEXP is the worksheet what is the C? [2],1,TOEPIEXP!C[40])" What is the [2] ? The 1 is the criteria. TOEPIEXP is the work sheet. What is C? what is 40? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200612/1 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Question about a formula
jln
Just spotted I have brackets all over the place, they should be around the number...sorry -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Nick Hodge" wrote in message ... jln The [C]2 is R1C1 notation, the C is for 'Column' and the 2 is Column 2 so B and as it has no '[R]' refers to the whole column. (Lookup Range) The 1 is the criteria and the second [C]40 is the range that is summed Column 40 -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "jln via OfficeKB.com" <u25956@uwe wrote in message news:6afe660e3e8b6@uwe... I trying to fig out what the formula is doing/ FormulaR1C1 = "=SUMIF(TOEPIEXP!C[2],1,TOEPIEXP!C[40])" TOEPIEXP!C i know that TOEPIEXP is the worksheet what is the C? [2],1,TOEPIEXP!C[40])" What is the [2] ? The 1 is the criteria. TOEPIEXP is the work sheet. What is C? what is 40? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200612/1 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Question about a formula
Nick that what i thought but im not using column 2 im using 6. I have
recorded for about 20 lines all using column 6 and they all say 2. SO how can that be the column number? Nick Hodge wrote: jln The [C]2 is R1C1 notation, the C is for 'Column' and the 2 is Column 2 so B and as it has no '[R]' refers to the whole column. (Lookup Range) The 1 is the criteria and the second [C]40 is the range that is summed Column 40 I trying to fig out what the formula is doing/ [quoted text clipped - 5 lines] is the work sheet. What is C? what is 40? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200612/1 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Question about a formula
jln
Using R1C1 notation, everything is relative to the range the formula is in. you have clipped the part before FormulaR1C1 If this was Range("C1").FormulaR1C1 then C[2] would refer to Column E Does that make sense? -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "jln via OfficeKB.com" <u25956@uwe wrote in message news:6afea1e594442@uwe... Nick that what i thought but im not using column 2 im using 6. I have recorded for about 20 lines all using column 6 and they all say 2. SO how can that be the column number? Nick Hodge wrote: jln The [C]2 is R1C1 notation, the C is for 'Column' and the 2 is Column 2 so B and as it has no '[R]' refers to the whole column. (Lookup Range) The 1 is the criteria and the second [C]40 is the range that is summed Column 40 I trying to fig out what the formula is doing/ [quoted text clipped - 5 lines] is the work sheet. What is C? what is 40? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200612/1 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Question about a formula
Here is what it looks like when i record it
Range("D27").Select ActiveCell.FormulaR1C1 = "=SUMIF(TOEPIEXP!C[2],1,TOEPIEXP!C[34])-R[-2]C-R [-1]C" Nick Hodge wrote: jln Using R1C1 notation, everything is relative to the range the formula is in. you have clipped the part before FormulaR1C1 If this was Range("C1").FormulaR1C1 then C[2] would refer to Column E Does that make sense? Nick that what i thought but im not using column 2 im using 6. I have recorded for about 20 lines all using column 6 and they all say 2. SO how [quoted text clipped - 15 lines] is the work sheet. What is C? what is 40? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200612/1 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Question about a formula
The formula is displaying a relative relationship to the column that the
formula is in. For example: If your formula is in column 4 then 2 columns to the right is column 6. 40 columns to the right is column 44. Mike F "jln via OfficeKB.com" <u25956@uwe wrote in message news:6afea1e594442@uwe... Nick that what i thought but im not using column 2 im using 6. I have recorded for about 20 lines all using column 6 and they all say 2. SO how can that be the column number? Nick Hodge wrote: jln The [C]2 is R1C1 notation, the C is for 'Column' and the 2 is Column 2 so B and as it has no '[R]' refers to the whole column. (Lookup Range) The 1 is the criteria and the second [C]40 is the range that is summed Column 40 I trying to fig out what the formula is doing/ [quoted text clipped - 5 lines] is the work sheet. What is C? what is 40? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200612/1 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Question about a formula
jln
Then [2] refers to F, 2 columns to the right of D (D27) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "jln via OfficeKB.com" <u25956@uwe wrote in message news:6afed105b6b42@uwe... Here is what it looks like when i record it Range("D27").Select ActiveCell.FormulaR1C1 = "=SUMIF(TOEPIEXP!C[2],1,TOEPIEXP!C[34])-R[-2]C-R [-1]C" Nick Hodge wrote: jln Using R1C1 notation, everything is relative to the range the formula is in. you have clipped the part before FormulaR1C1 If this was Range("C1").FormulaR1C1 then C[2] would refer to Column E Does that make sense? Nick that what i thought but im not using column 2 im using 6. I have recorded for about 20 lines all using column 6 and they all say 2. SO how [quoted text clipped - 15 lines] is the work sheet. What is C? what is 40? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200612/1 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Newbie Formula Question - how to get formula to repeat in each subsequent row? | New Users to Excel | |||
| Formula Question... | New Users to Excel | |||
| Formula question | Excel Worksheet Functions | |||
| Another Formula Question | Excel Discussion (Misc queries) | |||
| Newbie Question - Subtraction Formula Question | Excel Discussion (Misc queries) | |||
Linear Mode
