| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
help on replacing this formula
Hi. I am new to excel macros/vba/worksheet functions although I have used VBA
in Access. I have a user who has a worksheet with this formula: ((($R23*U23)+($R24*U24)+($R25*U25)+($R26*U26)+($R2 7*U27)+($R28*U28)+($R29*U29)+($R30*U30)+($R31*U31) +($R32*U32)+($R33*U33)+($R34*U34)+($R35*U35)+($R36 *U36)+($R37*U37)+($R38*U38)+($R39*U39)+($R40*U40)+ ($R41*U41)+($R42*U42)+($R43*U43)+($R44*U44)+($R45* U45)+($R46*U46)+($R47*U47)+($R48*U48)+($R50*U50)+( $R51*U51)+($R52*U52)+($R53*U53)+($R54*U54)+($R55*U 55)+($R56*U56)+($R57*U57)+($R58*U58)+($R59*U59)+($ R60*U60)+($R61*U61)+($R62*U62)+($R63*U63)+($R64*U6 4)+($R65*U65)+($R66*U66)+($R67*U67)+($R68*U68)+($R 69*U69)+($R70*U70)+($R71*U71)+($R72*U72)+($R73*U73 )+($R74*U74)+($R75*U75)+($R76*U76)+($R77*U77)+($R7 8*U78)+($R79*U79)+($R80*U80)+($R81*U81)+($R82*U82) +($R83*U83)+($R84*U84)+($R85*U85)+($R86*U86)+($R87 *U87)+($R88*U88)+($R89*U89)+($R90*U90)+($R91*U91)+ ($R92*U92)+($R93*U93)+($R94*U94)+($R95*U95)+($R96* U96)+($R97*U97)+($R98*U98)+($R99*U99)+($R100*U100) +($R101*U101)+($R102*U102)+($R103*U103)+($R104*U10 4)+($R105*U105)+($R106*U106)+($R107*U107)+($R108*U 108)+($R109*U109)+($R110*U110)+($R111*U111)+($R126 *U126))*U21)/1000 and really all it is doing is multiplying column r by U and then accumulating the results. I will also have to do the same thing by multiplying column R by V and then accumulating the results, etc. I am guessing this there is a very basic way to do it in excel. I know the data on the spread sheet does contain some blank rows, if that makes a difference. Can anyone help me get started on this? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
help on replacing this formula
try
=sumproduct(R23:R126,U23:U126)*U21/1000 "Abbey Normal" wrote: Hi. I am new to excel macros/vba/worksheet functions although I have used VBA in Access. I have a user who has a worksheet with this formula: ((($R23*U23)+($R24*U24)+($R25*U25)+($R26*U26)+($R2 7*U27)+($R28*U28)+($R29*U29)+($R30*U30)+($R31*U31) +($R32*U32)+($R33*U33)+($R34*U34)+($R35*U35)+($R36 *U36)+($R37*U37)+($R38*U38)+($R39*U39)+($R40*U40)+ ($R41*U41)+($R42*U42)+($R43*U43)+($R44*U44)+($R45* U45)+($R46*U46)+($R47*U47)+($R48*U48)+($R50*U50)+( $R51*U51)+($R52*U52)+($R53*U53)+($R54*U54)+($R55*U 55)+($R56*U56)+($R57*U57)+($R58*U58)+($R59*U59)+($ R60*U60)+($R61*U61)+($R62*U62)+($R63*U63)+($R64*U6 4)+($R65*U65)+($R66*U66)+($R67*U67)+($R68*U68)+($R 69*U69)+($R70*U70)+($R71*U71)+($R72*U72)+($R73*U73 )+($R74*U74)+($R75*U75)+($R76*U76)+($R77*U77)+($R7 8*U78)+($R79*U79)+($R80*U80)+($R81*U81)+($R82*U82) +($R83*U83)+($R84*U84)+($R85*U85)+($R86*U86)+($R87 *U87)+($R88*U88)+($R89*U89)+($R90*U90)+($R91*U91)+ ($R92*U92)+($R93*U93)+($R94*U94)+($R95*U95)+($R96* U96)+($R97*U97)+($R98*U98)+($R99*U99)+($R100*U100) +($R101*U101)+($R102*U102)+($R103*U103)+($R104*U10 4)+($R105*U105)+($R106*U106)+($R107*U107)+($R108*U 108)+($R109*U109)+($R110*U110)+($R111*U111)+($R126 *U126))*U21)/1000 and really all it is doing is multiplying column r by U and then accumulating the results. I will also have to do the same thing by multiplying column R by V and then accumulating the results, etc. I am guessing this there is a very basic way to do it in excel. I know the data on the spread sheet does contain some blank rows, if that makes a difference. Can anyone help me get started on this? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
help on replacing this formula
Hi,
You can use a helper column say X and enter the formula below in X1 and the copy drag down to where ever you need say X100 and sume column X in the cell X101: =R1*U1 Thanks, -- Farhad Hodjat "Abbey Normal" wrote: Hi. I am new to excel macros/vba/worksheet functions although I have used VBA in Access. I have a user who has a worksheet with this formula: ((($R23*U23)+($R24*U24)+($R25*U25)+($R26*U26)+($R2 7*U27)+($R28*U28)+($R29*U29)+($R30*U30)+($R31*U31) +($R32*U32)+($R33*U33)+($R34*U34)+($R35*U35)+($R36 *U36)+($R37*U37)+($R38*U38)+($R39*U39)+($R40*U40)+ ($R41*U41)+($R42*U42)+($R43*U43)+($R44*U44)+($R45* U45)+($R46*U46)+($R47*U47)+($R48*U48)+($R50*U50)+( $R51*U51)+($R52*U52)+($R53*U53)+($R54*U54)+($R55*U 55)+($R56*U56)+($R57*U57)+($R58*U58)+($R59*U59)+($ R60*U60)+($R61*U61)+($R62*U62)+($R63*U63)+($R64*U6 4)+($R65*U65)+($R66*U66)+($R67*U67)+($R68*U68)+($R 69*U69)+($R70*U70)+($R71*U71)+($R72*U72)+($R73*U73 )+($R74*U74)+($R75*U75)+($R76*U76)+($R77*U77)+($R7 8*U78)+($R79*U79)+($R80*U80)+($R81*U81)+($R82*U82) +($R83*U83)+($R84*U84)+($R85*U85)+($R86*U86)+($R87 *U87)+($R88*U88)+($R89*U89)+($R90*U90)+($R91*U91)+ ($R92*U92)+($R93*U93)+($R94*U94)+($R95*U95)+($R96* U96)+($R97*U97)+($R98*U98)+($R99*U99)+($R100*U100) +($R101*U101)+($R102*U102)+($R103*U103)+($R104*U10 4)+($R105*U105)+($R106*U106)+($R107*U107)+($R108*U 108)+($R109*U109)+($R110*U110)+($R111*U111)+($R126 *U126))*U21)/1000 and really all it is doing is multiplying column r by U and then accumulating the results. I will also have to do the same thing by multiplying column R by V and then accumulating the results, etc. I am guessing this there is a very basic way to do it in excel. I know the data on the spread sheet does contain some blank rows, if that makes a difference. Can anyone help me get started on this? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
help on replacing this formula
=SUMPRODUCT(R23:R111,U23:U111) is it true it skips rows from R111 to R126 at the end and that the total is multiplied by U21 and then divided by 1000? If so =((SUMPRODUCT(R23:R111,U23:U111)+(R126*U123))*U21)/1000 -- Regards, Peo Sjoblom "Abbey Normal" wrote in message ... Hi. I am new to excel macros/vba/worksheet functions although I have used VBA in Access. I have a user who has a worksheet with this formula: ((($R23*U23)+($R24*U24)+($R25*U25)+($R26*U26)+($R2 7*U27)+($R28*U28)+($R29*U29)+($R30*U30)+($R31*U31) +($R32*U32)+($R33*U33)+($R34*U34)+($R35*U35)+($R36 *U36)+($R37*U37)+($R38*U38)+($R39*U39)+($R40*U40)+ ($R41*U41)+($R42*U42)+($R43*U43)+($R44*U44)+($R45* U45)+($R46*U46)+($R47*U47)+($R48*U48)+($R50*U50)+( $R51*U51)+($R52*U52)+($R53*U53)+($R54*U54)+($R55*U 55)+($R56*U56)+($R57*U57)+($R58*U58)+($R59*U59)+($ R60*U60)+($R61*U61)+($R62*U62)+($R63*U63)+($R64*U6 4)+($R65*U65)+($R66*U66)+($R67*U67)+($R68*U68)+($R 69*U69)+($R70*U70)+($R71*U71)+($R72*U72)+($R73*U73 )+($R74*U74)+($R75*U75)+($R76*U76)+($R77*U77)+($R7 8*U78)+($R79*U79)+($R80*U80)+($R81*U81)+($R82*U82) +($R83*U83)+($R84*U84)+($R85*U85)+($R86*U86)+($R87 *U87)+($R88*U88)+($R89*U89)+($R90*U90)+($R91*U91)+ ($R92*U92)+($R93*U93)+($R94*U94)+($R95*U95)+($R96* U96)+($R97*U97)+($R98*U98)+($R99*U99)+($R100*U100) +($R101*U101)+($R102*U102)+($R103*U103)+($R104*U10 4)+($R105*U105)+($R106*U106)+($R107*U107)+($R108*U 108)+($R109*U109)+($R110*U110)+($R111*U111)+($R126 *U126))*U21)/1000 and really all it is doing is multiplying column r by U and then accumulating the results. I will also have to do the same thing by multiplying column R by V and then accumulating the results, etc. I am guessing this there is a very basic way to do it in excel. I know the data on the spread sheet does contain some blank rows, if that makes a difference. Can anyone help me get started on this? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
help on replacing this formula
WOW! What an elegant solution. Thanks very much, works perfectly!
"Peo Sjoblom" wrote: =SUMPRODUCT(R23:R111,U23:U111) is it true it skips rows from R111 to R126 at the end and that the total is multiplied by U21 and then divided by 1000? If so =((SUMPRODUCT(R23:R111,U23:U111)+(R126*U123))*U21)/1000 -- Regards, Peo Sjoblom "Abbey Normal" wrote in message ... Hi. I am new to excel macros/vba/worksheet functions although I have used VBA in Access. I have a user who has a worksheet with this formula: ((($R23*U23)+($R24*U24)+($R25*U25)+($R26*U26)+($R2 7*U27)+($R28*U28)+($R29*U29)+($R30*U30)+($R31*U31) +($R32*U32)+($R33*U33)+($R34*U34)+($R35*U35)+($R36 *U36)+($R37*U37)+($R38*U38)+($R39*U39)+($R40*U40)+ ($R41*U41)+($R42*U42)+($R43*U43)+($R44*U44)+($R45* U45)+($R46*U46)+($R47*U47)+($R48*U48)+($R50*U50)+( $R51*U51)+($R52*U52)+($R53*U53)+($R54*U54)+($R55*U 55)+($R56*U56)+($R57*U57)+($R58*U58)+($R59*U59)+($ R60*U60)+($R61*U61)+($R62*U62)+($R63*U63)+($R64*U6 4)+($R65*U65)+($R66*U66)+($R67*U67)+($R68*U68)+($R 69*U69)+($R70*U70)+($R71*U71)+($R72*U72)+($R73*U73 )+($R74*U74)+($R75*U75)+($R76*U76)+($R77*U77)+($R7 8*U78)+($R79*U79)+($R80*U80)+($R81*U81)+($R82*U82) +($R83*U83)+($R84*U84)+($R85*U85)+($R86*U86)+($R87 *U87)+($R88*U88)+($R89*U89)+($R90*U90)+($R91*U91)+ ($R92*U92)+($R93*U93)+($R94*U94)+($R95*U95)+($R96* U96)+($R97*U97)+($R98*U98)+($R99*U99)+($R100*U100) +($R101*U101)+($R102*U102)+($R103*U103)+($R104*U10 4)+($R105*U105)+($R106*U106)+($R107*U107)+($R108*U 108)+($R109*U109)+($R110*U110)+($R111*U111)+($R126 *U126))*U21)/1000 and really all it is doing is multiplying column r by U and then accumulating the results. I will also have to do the same thing by multiplying column R by V and then accumulating the results, etc. I am guessing this there is a very basic way to do it in excel. I know the data on the spread sheet does contain some blank rows, if that makes a difference. Can anyone help me get started on this? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
help on replacing this formula
A small typo there?
=((SUMPRODUCT(R23:R111,U23:U111)+(R126*U126))*U21)/1000 ? -- David Biddulph "Peo Sjoblom" wrote in message ... =SUMPRODUCT(R23:R111,U23:U111) is it true it skips rows from R111 to R126 at the end and that the total is multiplied by U21 and then divided by 1000? If so =((SUMPRODUCT(R23:R111,U23:U111)+(R126*U123))*U21)/1000 "Abbey Normal" wrote in message ... Hi. I am new to excel macros/vba/worksheet functions although I have used VBA in Access. I have a user who has a worksheet with this formula: ((($R23*U23)+($R24*U24)+($R25*U25)+($R26*U26)+($R2 7*U27)+($R28*U28)+($R29*U29)+($R30*U30)+($R31*U31) +($R32*U32)+($R33*U33)+($R34*U34)+($R35*U35)+($R36 *U36)+($R37*U37)+($R38*U38)+($R39*U39)+($R40*U40)+ ($R41*U41)+($R42*U42)+($R43*U43)+($R44*U44)+($R45* U45)+($R46*U46)+($R47*U47)+($R48*U48)+($R50*U50)+( $R51*U51)+($R52*U52)+($R53*U53)+($R54*U54)+($R55*U 55)+($R56*U56)+($R57*U57)+($R58*U58)+($R59*U59)+($ R60*U60)+($R61*U61)+($R62*U62)+($R63*U63)+($R64*U6 4)+($R65*U65)+($R66*U66)+($R67*U67)+($R68*U68)+($R 69*U69)+($R70*U70)+($R71*U71)+($R72*U72)+($R73*U73 )+($R74*U74)+($R75*U75)+($R76*U76)+($R77*U77)+($R7 8*U78)+($R79*U79)+($R80*U80)+($R81*U81)+($R82*U82) +($R83*U83)+($R84*U84)+($R85*U85)+($R86*U86)+($R87 *U87)+($R88*U88)+($R89*U89)+($R90*U90)+($R91*U91)+ ($R92*U92)+($R93*U93)+($R94*U94)+($R95*U95)+($R96* U96)+($R97*U97)+($R98*U98)+($R99*U99)+($R100*U100) +($R101*U101)+($R102*U102)+($R103*U103)+($R104*U10 4)+($R105*U105)+($R106*U106)+($R107*U107)+($R108*U 108)+($R109*U109)+($R110*U110)+($R111*U111)+($R126 *U126))*U21)/1000 and really all it is doing is multiplying column r by U and then accumulating the results. I will also have to do the same thing by multiplying column R by V and then accumulating the results, etc. I am guessing this there is a very basic way to do it in excel. I know the data on the spread sheet does contain some blank rows, if that makes a difference. Can anyone help me get started on this? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
help on replacing this formula
Thanks
Peo "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... A small typo there? =((SUMPRODUCT(R23:R111,U23:U111)+(R126*U126))*U21)/1000 ? -- David Biddulph "Peo Sjoblom" wrote in message ... =SUMPRODUCT(R23:R111,U23:U111) is it true it skips rows from R111 to R126 at the end and that the total is multiplied by U21 and then divided by 1000? If so =((SUMPRODUCT(R23:R111,U23:U111)+(R126*U123))*U21)/1000 "Abbey Normal" wrote in message ... Hi. I am new to excel macros/vba/worksheet functions although I have used VBA in Access. I have a user who has a worksheet with this formula: ((($R23*U23)+($R24*U24)+($R25*U25)+($R26*U26)+($R2 7*U27)+($R28*U28)+($R29*U29)+($R30*U30)+($R31*U31) +($R32*U32)+($R33*U33)+($R34*U34)+($R35*U35)+($R36 *U36)+($R37*U37)+($R38*U38)+($R39*U39)+($R40*U40)+ ($R41*U41)+($R42*U42)+($R43*U43)+($R44*U44)+($R45* U45)+($R46*U46)+($R47*U47)+($R48*U48)+($R50*U50)+( $R51*U51)+($R52*U52)+($R53*U53)+($R54*U54)+($R55*U 55)+($R56*U56)+($R57*U57)+($R58*U58)+($R59*U59)+($ R60*U60)+($R61*U61)+($R62*U62)+($R63*U63)+($R64*U6 4)+($R65*U65)+($R66*U66)+($R67*U67)+($R68*U68)+($R 69*U69)+($R70*U70)+($R71*U71)+($R72*U72)+($R73*U73 )+($R74*U74)+($R75*U75)+($R76*U76)+($R77*U77)+($R7 8*U78)+($R79*U79)+($R80*U80)+($R81*U81)+($R82*U82) +($R83*U83)+($R84*U84)+($R85*U85)+($R86*U86)+($R87 *U87)+($R88*U88)+($R89*U89)+($R90*U90)+($R91*U91)+ ($R92*U92)+($R93*U93)+($R94*U94)+($R95*U95)+($R96* U96)+($R97*U97)+($R98*U98)+($R99*U99)+($R100*U100) +($R101*U101)+($R102*U102)+($R103*U103)+($R104*U10 4)+($R105*U105)+($R106*U106)+($R107*U107)+($R108*U 108)+($R109*U109)+($R110*U110)+($R111*U111)+($R126 *U126))*U21)/1000 and really all it is doing is multiplying column r by U and then accumulating the results. I will also have to do the same thing by multiplying column R by V and then accumulating the results, etc. I am guessing this there is a very basic way to do it in excel. I know the data on the spread sheet does contain some blank rows, if that makes a difference. Can anyone help me get started on this? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Replacing half a formula | Excel Discussion (Misc queries) | |||
| replacing text within a formula | Excel Worksheet Functions | |||
| AFTER REPLACING FORMULA WITH VALUES, HOW DO I ADD THE COLUMN? | Excel Discussion (Misc queries) | |||
| Replacing letters for figures in formula | Excel Discussion (Misc queries) | |||
| Replacing Values in Cells Via Formula | Excel Worksheet Functions | |||
Linear Mode
