FORMULA ERROR

[Guest]

I have seen in this website you people can resolve Excell issues.
I have one formula problem in EXCEL 2003
I was preparing sheet where rows are A TO AK AND Columns are 1 to 550 i am
giving the example below.


A1 PRODUCT CODE B1 Packsize in Litre C1 Quantity IN CANS
A2 42112 B2 1 C2 500
A3 42113 B3 10 C3 4000
A4 42114 B4 9 C4 2500
A5 42115 B5 11 C5 400
A550 41156 B550 C550 Total
Litre req.from C2 to C549 like C2*B2 and C3*B3 i have already applied this
formula for the sake total litre in this way.
=(C2*B$2$)+(C3*B$3$) ........(C107*B$107$)
when I was writing (C108
I have recevied message "FORMULA IS TOO LONG"

Can you please help about this error message or is there any other way to
calculate this sheet in a short way.

Thanks and Regards
ZEESHAN ANIS

 

[Duncan]

=SUM(C2:C250)*SUM(B2:B250)

Put your two ranges in and Array enter this, it should be
correct........

(array enter = put formula and instead of pressing Enter, press
CTRL+SHIFT+ENTER and you will get curly brackets around it)

Let us know if this works

Duncan

 

[Duncan]

When I say "put your two ranges in" I mean the the C column and the B
column, as you will see I only catered for the range being 250 rows
long, it might be longer I didnt know your full range.

Duncan

 

[SIR Knight]

Zeeshan,

I think you are making this too complicated. You appear to only want
the overall, in which case:

If you add a column D, then multiply B and C for each row (eg.
"=C5*B5"). Then total column D.

Alternatively add a total to each column, B & C and the multiply them
together

You are getting the "Too Long" error as you are putting too many
entries into one calculation.

Steve

 

[Duncan]

Just to be a pain in the bum.....

=SUM(A2:A11)*SUM(B2:B11) / 10

Put this formula instead, for some reason (I found out after testing)
you need to divide the initial answer by 10 to get the right answer.
dont know why, but its right and thats all that matters really.

(Dont forget to Array-Enter it or it wont work!)

Regards

Duncan

 

[SIR Knight]

Zeeshan,

I think you are making this too complicated. You appear to only want
the overall, in which case:

If you add a column D, then multiply B and C for each row (eg.
"=C5*B5"). Then total column D.

Alternatively add a total to each column, B & C and the multiply them
together

You are getting the "Too Long" error as you are putting too many
entries into one calculation.

Steve

 

[Duncan]

Steve,

Dont you think that an array formula to give the result might be a
little easier to implement than an additional column?

Personally I would have had the additional column from the start
because I am not really a genius on array formulas and only touch them
to investigate with, but in this case the user might already be
constrained as to the layout of the spreadsheet

Duncan

 

[SIR Knight]

Duncan,

I do agree that using your array idea will work, but as you say the
simplest solution is the extra column, or just to use the totals from
each column already there.

However, there is no need to divide by 10 for the right answer, I am
unsure as to why you need to do this.

Steve

 

[Duncan]

Steve,

I am also unsure why this needs doing, I just tried it manually (with
extra column) and array way and the comparison showed an increase of
*10.......

may or may not need it then, just have to play with it and see until
you get the right total, my sheet may have had a mistake on it after
all.

Duncan

 

[Guest]

Duncan had the right idea, but the implementation is incorrect. Also, the
formula he suggest doesn't need to be array entered (but in either case,
returns the wrong answer because it adds up each column first, then does the
multiplication).

i.e. It multiplies the sum of column b by the sum of column c (rows 2 to
250).

this is how it should be correctly implemented:
=Sumproduct($C$2:$C$549,$B$2:$B$549)

this doesn't need to be array entered either.

 

[Guest]

(Dont forget to Array-Enter it or it wont work!)

Try it. I get the same wrong answer whether it is entered normally or array
entered.