if sum more than 5 cells not in consecutive order, I can use the AutoSum and
select the cells I want using the control button or type in =SUM(C23,C34,c40,
c45, c50,c56,c63,...). However, I receive an error saying I have too many
arguments. Why is this displaying?
Because you have too many arguments.
XL functions are limited to 30 arguments.
One workaround: Select your cells and name them, say "sum_range" by
entering "sum_range" (without quotes) in the Name box at the left side
of the formula bar.
Then you can use one argument:
=SUM(sum_range)
In article <6D31B10F-51D9-4BDE-B47F-127FE724C86E@microsoft.com>,
kermit <kermit@discussions.microsoft.com> wrote:
> if sum more than 5 cells not in consecutive order, I can use the AutoSum and
> select the cells I want using the control button or type in =SUM(C23,C34,c40,
> c45, c50,c56,c63,...). However, I receive an error saying I have too many
> arguments. Why is this displaying?
Same problem, but I want to sum every 5th line (Multibles the of 30 max) from
spreadsheet imported from "Home Depot's WorkSite CD". None of the functions
came over, just the resultant values.
"JE McGimpsey" wrote:
> Because you have too many arguments.
>
> XL functions are limited to 30 arguments.
>
> One workaround: Select your cells and name them, say "sum_range" by
> entering "sum_range" (without quotes) in the Name box at the left side
> of the formula bar.
>
> Then you can use one argument:
>
> =SUM(sum_range)
>
> In article <6D31B10F-51D9-4BDE-B47F-127FE724C86E@microsoft.com>,
> kermit <kermit@discussions.microsoft.com> wrote:
>
> > if sum more than 5 cells not in consecutive order, I can use the AutoSum and
> > select the cells I want using the control button or type in =SUM(C23,C34,c40,
> > c45, c50,c56,c63,...). However, I receive an error saying I have too many
> > arguments. Why is this displaying?
>
Assuming you want to sum rows 1, 6, 11, etc
=SUMPRODUCT((MOD(ROW(A1:A100),5)=1)*(A1:A100))
To sum rows 5, 10, 15, etc, change =1 to =0
On Fri, 18 Feb 2005 19:41:01 -0800, "Buildourhouse"
<Buildourhouse@discussions.microsoft.com> wrote:
>Same problem, but I want to sum every 5th line (Multibles the of 30 max) from
>spreadsheet imported from "Home Depot's WorkSite CD". None of the functions
>came over, just the resultant values.
>
>"JE McGimpsey" wrote:
>
>> Because you have too many arguments.
>>
>> XL functions are limited to 30 arguments.
>>
>> One workaround: Select your cells and name them, say "sum_range" by
>> entering "sum_range" (without quotes) in the Name box at the left side
>> of the formula bar.
>>
>> Then you can use one argument:
>>
>> =SUM(sum_range)
>>
>> In article <6D31B10F-51D9-4BDE-B47F-127FE724C86E@microsoft.com>,
>> kermit <kermit@discussions.microsoft.com> wrote:
>>
>> > if sum more than 5 cells not in consecutive order, I can use the AutoSum
and
>> > select the cells I want using the control button or type in
=SUM(C23,C34,c40,
>> > c45, c50,c56,c63,...). However, I receive an error saying I have too
many
>> > arguments. Why is this displaying?
>>
Thank you, Myrna. This spreadsheet is a material list that repeats it's
format/layout every 5th line. Can I set up a sheet that will have functions
automatically duplicated every 5th line for each material entry? 1st line
blank.
Next 2 lines are just descriptions (text),
4th is (B-hourly rate) (C-hours) (E-material cost) (F-labor cost)
(G-equipment cost) (H-total unit cost).
5th line (A-# of units) (C,E,F,G,H as above * # of units).
These are then totaled at bottom (Previous question you answered)
thanks
"Myrna Larson" wrote:
> Assuming you want to sum rows 1, 6, 11, etc
>
> =SUMPRODUCT((MOD(ROW(A1:A100),5)=1)*(A1:A100))
>
> To sum rows 5, 10, 15, etc, change =1 to =0
>
> On Fri, 18 Feb 2005 19:41:01 -0800, "Buildourhouse"
> <Buildourhouse@discussions.microsoft.com> wrote:
>
> >Same problem, but I want to sum every 5th line (Multibles the of 30 max) from
> >spreadsheet imported from "Home Depot's WorkSite CD". None of the functions
> >came over, just the resultant values.
> >
> >"JE McGimpsey" wrote:
> >
> >> Because you have too many arguments.
> >>
> >> XL functions are limited to 30 arguments.
> >>
> >> One workaround: Select your cells and name them, say "sum_range" by
> >> entering "sum_range" (without quotes) in the Name box at the left side
> >> of the formula bar.
> >>
> >> Then you can use one argument:
> >>
> >> =SUM(sum_range)
> >>
> >> In article <6D31B10F-51D9-4BDE-B47F-127FE724C86E@microsoft.com>,
> >> kermit <kermit@discussions.microsoft.com> wrote:
> >>
> >> > if sum more than 5 cells not in consecutive order, I can use the AutoSum
> and
> >> > select the cells I want using the control button or type in
> =SUM(C23,C34,c40,
> >> > c45, c50,c56,c63,...). However, I receive an error saying I have too
> many
> >> > arguments. Why is this displaying?
> >>
>
>
Set up the first 5 rows as you want them (say rows 2:6). Then select all 5
rows and Edit/Copy. Then select a multiple of 5 rows below that (say 7:46 for
an additional 8 blocks) and Edit/Paste.
On Sun, 20 Feb 2005 00:07:02 -0800, "buildourhouse"
<buildourhouse@discussions.microsoft.com> wrote:
>Thank you, Myrna. This spreadsheet is a material list that repeats it's
>format/layout every 5th line. Can I set up a sheet that will have functions
>automatically duplicated every 5th line for each material entry? 1st line
>blank.
>Next 2 lines are just descriptions (text),
>4th is (B-hourly rate) (C-hours) (E-material cost) (F-labor cost)
>(G-equipment cost) (H-total unit cost).
>5th line (A-# of units) (C,E,F,G,H as above * # of units).
>These are then totaled at bottom (Previous question you answered)
>thanks
>
>"Myrna Larson" wrote:
>
>> Assuming you want to sum rows 1, 6, 11, etc
>>
>> =SUMPRODUCT((MOD(ROW(A1:A100),5)=1)*(A1:A100))
>>
>> To sum rows 5, 10, 15, etc, change =1 to =0
>>
>> On Fri, 18 Feb 2005 19:41:01 -0800, "Buildourhouse"
>> <Buildourhouse@discussions.microsoft.com> wrote:
>>
>> >Same problem, but I want to sum every 5th line (Multibles the of 30 max)
from
>> >spreadsheet imported from "Home Depot's WorkSite CD". None of the
functions
>> >came over, just the resultant values.
>> >
>> >"JE McGimpsey" wrote:
>> >
>> >> Because you have too many arguments.
>> >>
>> >> XL functions are limited to 30 arguments.
>> >>
>> >> One workaround: Select your cells and name them, say "sum_range" by
>> >> entering "sum_range" (without quotes) in the Name box at the left side
>> >> of the formula bar.
>> >>
>> >> Then you can use one argument:
>> >>
>> >> =SUM(sum_range)
>> >>
>> >> In article <6D31B10F-51D9-4BDE-B47F-127FE724C86E@microsoft.com>,
>> >> kermit <kermit@discussions.microsoft.com> wrote:
>> >>
>> >> > if sum more than 5 cells not in consecutive order, I can use the
AutoSum
>> and
>> >> > select the cells I want using the control button or type in
>> =SUM(C23,C34,c40,
>> >> > c45, c50,c56,c63,...). However, I receive an error saying I have too
>> many
>> >> > arguments. Why is this displaying?
>> >>
>>
>>
Thanks again, Myrna. this would be ok if i were manually entering the
material one at a time. I am importing an existing table from another
program into excel. All the text and values transfer, but none of the
formulas. I have 400 lines so far in this table & it will grow as I enter
more materials using the original program. I would like to the update the
excell table with the new entries form the first program as more materials
are identified. Can i use some kind of macro to replace the values in the
excel table copied from the original program with formulas en masse? I'm
downloading this table to my pocket pc for use on the road.
Thank you. Bill
"Myrna Larson" wrote:
> Set up the first 5 rows as you want them (say rows 2:6). Then select all 5
> rows and Edit/Copy. Then select a multiple of 5 rows below that (say 7:46 for
> an additional 8 blocks) and Edit/Paste.
>
> On Sun, 20 Feb 2005 00:07:02 -0800, "buildourhouse"
> <buildourhouse@discussions.microsoft.com> wrote:
>
> >Thank you, Myrna. This spreadsheet is a material list that repeats it's
> >format/layout every 5th line. Can I set up a sheet that will have functions
> >automatically duplicated every 5th line for each material entry? 1st line
> >blank.
> >Next 2 lines are just descriptions (text),
> >4th is (B-hourly rate) (C-hours) (E-material cost) (F-labor cost)
> >(G-equipment cost) (H-total unit cost).
> >5th line (A-# of units) (C,E,F,G,H as above * # of units).
> >These are then totaled at bottom (Previous question you answered)
> >thanks
> >
> >"Myrna Larson" wrote:
> >
> >> Assuming you want to sum rows 1, 6, 11, etc
> >>
> >> =SUMPRODUCT((MOD(ROW(A1:A100),5)=1)*(A1:A100))
> >>
> >> To sum rows 5, 10, 15, etc, change =1 to =0
> >>
> >> On Fri, 18 Feb 2005 19:41:01 -0800, "Buildourhouse"
> >> <Buildourhouse@discussions.microsoft.com> wrote:
> >>
> >> >Same problem, but I want to sum every 5th line (Multibles the of 30 max)
> from
> >> >spreadsheet imported from "Home Depot's WorkSite CD". None of the
> functions
> >> >came over, just the resultant values.
> >> >
> >> >"JE McGimpsey" wrote:
> >> >
> >> >> Because you have too many arguments.
> >> >>
> >> >> XL functions are limited to 30 arguments.
> >> >>
> >> >> One workaround: Select your cells and name them, say "sum_range" by
> >> >> entering "sum_range" (without quotes) in the Name box at the left side
> >> >> of the formula bar.
> >> >>
> >> >> Then you can use one argument:
> >> >>
> >> >> =SUM(sum_range)
> >> >>
> >> >> In article <6D31B10F-51D9-4BDE-B47F-127FE724C86E@microsoft.com>,
> >> >> kermit <kermit@discussions.microsoft.com> wrote:
> >> >>
> >> >> > if sum more than 5 cells not in consecutive order, I can use the
> AutoSum
> >> and
> >> >> > select the cells I want using the control button or type in
> >> =SUM(C23,C34,c40,
> >> >> > c45, c50,c56,c63,...). However, I receive an error saying I have too
> >> many
> >> >> > arguments. Why is this displaying?
> >> >>
> >>
> >>
>
>
YOu want to replace literal values with formulas? Usually people want to go
the other way. It probably is possible to work up a macro, but not without
lots more information about the details of what you want to do, the layout of
the two files, etc.
On Sun, 20 Feb 2005 13:53:02 -0800, "buildourhouse"
<buildourhouse@discussions.microsoft.com> wrote:
>Thanks again, Myrna. this would be ok if i were manually entering the
>material one at a time. I am importing an existing table from another
>program into excel. All the text and values transfer, but none of the
>formulas. I have 400 lines so far in this table & it will grow as I enter
>more materials using the original program. I would like to the update the
>excell table with the new entries form the first program as more materials
>are identified. Can i use some kind of macro to replace the values in the
>excel table copied from the original program with formulas en masse? I'm
>downloading this table to my pocket pc for use on the road.
>Thank you. Bill
>
>
>"Myrna Larson" wrote:
>
>> Set up the first 5 rows as you want them (say rows 2:6). Then select all 5
>> rows and Edit/Copy. Then select a multiple of 5 rows below that (say 7:46
for
>> an additional 8 blocks) and Edit/Paste.
>>
>> On Sun, 20 Feb 2005 00:07:02 -0800, "buildourhouse"
>> <buildourhouse@discussions.microsoft.com> wrote:
>>
>> >Thank you, Myrna. This spreadsheet is a material list that repeats it's
>> >format/layout every 5th line. Can I set up a sheet that will have
functions
>> >automatically duplicated every 5th line for each material entry? 1st line
>> >blank.
>> >Next 2 lines are just descriptions (text),
>> >4th is (B-hourly rate) (C-hours) (E-material cost) (F-labor cost)
>> >(G-equipment cost) (H-total unit cost).
>> >5th line (A-# of units) (C,E,F,G,H as above * # of units).
>> >These are then totaled at bottom (Previous question you answered)
>> >thanks
>> >
>> >"Myrna Larson" wrote:
>> >
>> >> Assuming you want to sum rows 1, 6, 11, etc
>> >>
>> >> =SUMPRODUCT((MOD(ROW(A1:A100),5)=1)*(A1:A100))
>> >>
>> >> To sum rows 5, 10, 15, etc, change =1 to =0
>> >>
>> >> On Fri, 18 Feb 2005 19:41:01 -0800, "Buildourhouse"
>> >> <Buildourhouse@discussions.microsoft.com> wrote:
>> >>
>> >> >Same problem, but I want to sum every 5th line (Multibles the of 30
max)
>> from
>> >> >spreadsheet imported from "Home Depot's WorkSite CD". None of the
>> functions
>> >> >came over, just the resultant values.
>> >> >
>> >> >"JE McGimpsey" wrote:
>> >> >
>> >> >> Because you have too many arguments.
>> >> >>
>> >> >> XL functions are limited to 30 arguments.
>> >> >>
>> >> >> One workaround: Select your cells and name them, say "sum_range" by
>> >> >> entering "sum_range" (without quotes) in the Name box at the left
side
>> >> >> of the formula bar.
>> >> >>
>> >> >> Then you can use one argument:
>> >> >>
>> >> >> =SUM(sum_range)
>> >> >>
>> >> >> In article <6D31B10F-51D9-4BDE-B47F-127FE724C86E@microsoft.com>,
>> >> >> kermit <kermit@discussions.microsoft.com> wrote:
>> >> >>
>> >> >> > if sum more than 5 cells not in consecutive order, I can use the
>> AutoSum
>> >> and
>> >> >> > select the cells I want using the control button or type in
>> >> =SUM(C23,C34,c40,
>> >> >> > c45, c50,c56,c63,...). However, I receive an error saying I have
too
>> >> many
>> >> >> > arguments. Why is this displaying?
>> >> >>
>> >>
>> >>
>>
>>
Yes, replace values with formulas. this allows me to manipulate data on the
road to create a new list from the master. The program I'm starting with is
one that "The Home Depot" store's Contractor Services gives to contractors
(me) to look up, tabulate, & order merchandise. It's created by "The
Craftsman Book Co." (www.costbook.com). Once I have selected each item, I
may input how many of each & change it's cost and rate & Hours for installing
that item. Hit return & it loads it into a form, taking up 5 lines per item,
& gives running totals at the bottom (my 1st question). When I save it as a
tabbed table, call it into excel, it only transfers the text and numeric
values, not the formulas that allow me to change those values. So you can
see what this looks like, can I send or post this somewhere. Oh, this
Craftsman program does download this to Quickbooks, but I don't have it. I
have excel. Don't mean to use up sooooo much of your time, but I'm lost &
inexperienced at excel. If you can provide me with followup, I would greatly
appriciate it. If not, thanks for your help.
Bill
"Myrna Larson" wrote:
> YOu want to replace literal values with formulas? Usually people want to go
> the other way. It probably is possible to work up a macro, but not without
> lots more information about the details of what you want to do, the layout of
> the two files, etc.
>
> On Sun, 20 Feb 2005 13:53:02 -0800, "buildourhouse"
> <buildourhouse@discussions.microsoft.com> wrote:
>
> >Thanks again, Myrna. this would be ok if i were manually entering the
> >material one at a time. I am importing an existing table from another
> >program into excel. All the text and values transfer, but none of the
> >formulas. I have 400 lines so far in this table & it will grow as I enter
> >more materials using the original program. I would like to the update the
> >excell table with the new entries form the first program as more materials
> >are identified. Can i use some kind of macro to replace the values in the
> >excel table copied from the original program with formulas en masse? I'm
> >downloading this table to my pocket pc for use on the road.
> >Thank you. Bill
> >
> >
> >"Myrna Larson" wrote:
> >
> >> Set up the first 5 rows as you want them (say rows 2:6). Then select all 5
> >> rows and Edit/Copy. Then select a multiple of 5 rows below that (say 7:46
> for
> >> an additional 8 blocks) and Edit/Paste.
> >>
> >> On Sun, 20 Feb 2005 00:07:02 -0800, "buildourhouse"
> >> <buildourhouse@discussions.microsoft.com> wrote:
> >>
> >> >Thank you, Myrna. This spreadsheet is a material list that repeats it's
> >> >format/layout every 5th line. Can I set up a sheet that will have
> functions
> >> >automatically duplicated every 5th line for each material entry? 1st line
> >> >blank.
> >> >Next 2 lines are just descriptions (text),
> >> >4th is (B-hourly rate) (C-hours) (E-material cost) (F-labor cost)
> >> >(G-equipment cost) (H-total unit cost).
> >> >5th line (A-# of units) (C,E,F,G,H as above * # of units).
> >> >These are then totaled at bottom (Previous question you answered)
> >> >thanks
> >> >
> >> >"Myrna Larson" wrote:
> >> >
> >> >> Assuming you want to sum rows 1, 6, 11, etc
> >> >>
> >> >> =SUMPRODUCT((MOD(ROW(A1:A100),5)=1)*(A1:A100))
> >> >>
> >> >> To sum rows 5, 10, 15, etc, change =1 to =0
> >> >>
> >> >> On Fri, 18 Feb 2005 19:41:01 -0800, "Buildourhouse"
> >> >> <Buildourhouse@discussions.microsoft.com> wrote:
> >> >>
> >> >> >Same problem, but I want to sum every 5th line (Multibles the of 30
> max)
> >> from
> >> >> >spreadsheet imported from "Home Depot's WorkSite CD". None of the
> >> functions
> >> >> >came over, just the resultant values.
> >> >> >
> >> >> >"JE McGimpsey" wrote:
> >> >> >
> >> >> >> Because you have too many arguments.
> >> >> >>
> >> >> >> XL functions are limited to 30 arguments.
> >> >> >>
> >> >> >> One workaround: Select your cells and name them, say "sum_range" by
> >> >> >> entering "sum_range" (without quotes) in the Name box at the left
> side
> >> >> >> of the formula bar.
> >> >> >>
> >> >> >> Then you can use one argument:
> >> >> >>
> >> >> >> =SUM(sum_range)
> >> >> >>
> >> >> >> In article <6D31B10F-51D9-4BDE-B47F-127FE724C86E@microsoft.com>,
> >> >> >> kermit <kermit@discussions.microsoft.com> wrote:
> >> >> >>
> >> >> >> > if sum more than 5 cells not in consecutive order, I can use the
> >> AutoSum
> >> >> and
> >> >> >> > select the cells I want using the control button or type in
> >> >> =SUM(C23,C34,c40,
> >> >> >> > c45, c50,c56,c63,...). However, I receive an error saying I have
> too
> >> >> many
> >> >> >> > arguments. Why is this displaying?
> >> >> >>
> >> >>
> >> >>
> >>
> >>
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks