Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
FORMULA ERROR
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
FORMULA ERROR
=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 ZEESHAN ANIS wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
FORMULA ERROR
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 Duncan wrote: =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 ZEESHAN ANIS wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
FORMULA ERROR
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 Duncan wrote: 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 Duncan wrote: =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 ZEESHAN ANIS wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
FORMULA ERROR
(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. -- Regards, Tom Ogilvy "Duncan" wrote: 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 Duncan wrote: 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 Duncan wrote: =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 ZEESHAN ANIS wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
FORMULA ERROR
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
FORMULA ERROR
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
FORMULA ERROR
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 wrote: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
FORMULA ERROR
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
FORMULA ERROR
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 SIR Knight wrote: 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
FORMULA ERROR
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. -- Regards, Tom Ogilvy "ZEESHAN ANIS" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF formula returns error; abbreviating the formula | Excel Discussion (Misc queries) | |||
Formula Error-Error Message | Excel Programming | |||
Formula expected end of statement error, typing formula into cell as part of VBA macro | Excel Programming | |||
How do I replace "#N/A" error, to continue my formula w/o error? | Excel Worksheet Functions | |||
Formula error with Mac resulting in '#NAME' error | Excel Programming |