More than 30 arguments

[kristee]

how can I make it so that the cell will accept more than 30 arguments?
(thats what the 'pop-up' says when it refuses to let me finish the
equation....)
ps - i took a look at nesting, but because i am not looking to use
averages, i dont think it will work....
thanks for your help.

 

[Ron Rosenfeld]

how can I make it so that the cell will accept more than 30 arguments?
(thats what the 'pop-up' says when it refuses to let me finish the
equation....)
ps - i took a look at nesting, but because i am not looking to use
averages, i dont think it will work....
thanks for your help.

You cannot. You will need to take a different approach. Perhaps if you post
exactly what you are trying to do, advice will be forthcoming.
--ron

 

[firefytr]

Hi,

I'm assuming these are non-contiguous cells? If so, you may want t
look at other conditional Summing functions. A good one may b
SUMPRODUCT or SUMIF. Is there a certain criteria that this sum i
dependent on? If so, post details

 

[Frank Kabel]

Hi
and in addition to Ron: You may post also your current formula

 

[Ken Wright]

I'm guessing you're trying to do something like:-

=AVERAGE(A1,B1,C1,D1,E1,F1,G1.......................) etc

If so then you do not need to enter all the cells individually, you can enter a
range as a single argument, eg:-

=AVERAGE(A1:BC1)

 

[kristee]

The cells that I have to add are being pulled from 12 different wor
sheets. They are in different spots on the sheet too. (ie wksht 1 - C8
wksht 2 C10, wksht 3, C5 and C7, etc

 

[kristee]

Hi again,
Essentially what I am trying to do is on the 'Ongoing 2004' wksht
move 'Troy' info to 'House' as he is no longer with the company.
Thanks -

Attachment filename: ex book.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=64809

 

[hgrove]

kristee wrote...

Essentially what I am trying to do is on the 'Ongoing 2004'
wksht - move 'Troy' info to 'House' as he is no longer with the
company.

Attachments are useless since no one in their right mind would open
file posted by a stranger. That excelforum provides such a facility i
just more evidence that the people who run it are either overly trusin
fools or just don't understand the risks. Next time post your formula A
PLAIN TEXT in the body of your message.

This time you don't need to post the formula. You could sum sums.

=SUM(SUM(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,
21,22,23,24),SUM(25,26,27,28,29,30,31,32,33,34,35,36,37,38,
39,40,41,42,43,44,45,46,47,48),SUM(49,50,51,52,53,54,55,56,
57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72),SUM(73,74,
75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,
95,96))

You'll run into the 1,024 character limit on formula length well befor
you'd be able to write a sum of 29 sums each of 29 separate numbers

 

[Bob Umlas]

You don't need the extra SUM's if the things being summed are ranges:
=SUM((A1,A2,A3,A4,A5,A6,A7),(A8,A9,A10,A11,A12,A13,A14),(F3,g45,sheet6!z333)
)
etc.

 

[kristee]

here is the formula i am trying to add things to.

=SUM('Jan 04'!C5,'Feb 04'!C12,'Feb 04'!C5,'Mar 04'!C12,'Ma
04'!C5,'April 04'!C5,'May 04 '!C5,'June 04'!C5,'July 04'!C5,'Au
04'!C5,'Sept 04'!C5,'Oct 04'!C5,'Nov 04'!C5,'Dec 04'!C5,'Apri
04'!C12,'May 04 '!C12,'Jan 04'!C6,'Feb 04'!C6,'Mar 04'!C6,'Apri
04'!C6,'May 04 '!C6,'Jan 04'!C10,'Feb 04'!C10,'Mar 04'!C10,'Apri
04'!C10,'May 04 '!C10,'June 04'!C8,'July 04'!C8 )

I want to add ('May 04 '!C13,'June 04'!C10,'July 04'!C10) to it.

is there anything else i need to include to clarify my situation?


___________________________________

pardon my naivete on using <this> forum. its my first time

 

[hgrove]

Bob Umlas wrote...

You don't need the extra SUM's if the things being summed are
ranges:
=SUM((A1,A2,A3,A4,A5,A6,A7),(A8,A9,A10,A11,A12,A13,A14),
(F3,g45,sheet6!z333))

...

Tell you what, why don't you test your wonderful formula. When you'v
fixed the #VALUE! error that Excel is *CERTAIN* to return, you may se
why you'd need to offer some caveats on how to use this approach.

If the OP's arguments come from more than 30 different worksheets, SU
of SUMs would be necessary. If the arguments come from fewe
worksheets, one multiple cell range per worksheet would be sufficient
but can get wordy. e.g.,

=SUM((Jan!A1,Jan!B2,Jan!C3),(Feb!B1,Feb!C2,Feb!D3),
(Mar!C1,Mar!D2,Mar!E3),(Apr!D1,Apr!E2,Apr!F3)

 

[Frank Kabel]

Hi
try:
=SUM('Jan 04ec 04'!C5,'Feb 04:May 04'!C12,'Feb 04:May 04'!C6,'Jan
04:July 04'!C10,'June 04:July 04'!C8,'Jan 04'!C5:C6,'May 04 '!C13)

Note: You use different month ranges in this sum

 

[hgrove]

Frank Kabel wrote...

try:
=SUM('Jan 04ec 04'!C5,
'Feb 04:May 04'!C12,
'Feb 04:May 04'!C6,
'Jan 04:July 04'!C10,
'June 04:July 04'!C8,
'Jan 04'!C5:C6,
'May 04 '!C13)

...

Your first and sixth arguments both include 'Jan 04'!C5. Presumably
this wasn't intentional, but it does illustrate the need for care when
attempting to mix 2D and 3D ranges.

 

[Frank Kabel]

Regards
Frank Kabel
Frankfurt, Germany

Frank Kabel wrote...
..

Your first and sixth arguments both include 'Jan 04'!C5. Presumably
this wasn't intentional, but it does illustrate the need for care when
attempting to mix 2D and 3D ranges.

Hi Harlan

thanks for spotting this and you're right of beeing extra careful with
this kind of mixture
Frank

 

[kristee]

that worked! thanks sooooo much! and yes i will watch for duplications.
cheers all