Max number in cell

[ALLDREAMS]

Is there a way to increase the upper limit to a cell.
excel will wont calculate a cell it it exceeds 1024
characters.

Many thanks!

 

[Ken Wright]

Post the formula as far as you have it and we may be able to trim it down for
you.

 

[Peo Sjoblom]

Taken from Help

Length of cell contents (text) 32,767 characters. Only 1,024 display
in a cell; all 32,767 display in the formula bar


What do you mean by "wont calculate"?

A formula's limit is a 1024 characters and you can't go beyond that, you can
however name parts of the formula
under view>names>define to overcome that

 

[Guest]

this is the main part of the formula. if you could trim
this down, I would apply the changes to the rest of the
formula

=IF(OR((((MAXA(B12:U12)-U14)/(MAXA(B12:U12)-MINA
(B13:U13))))>90%,(((MAXA(B12:U12)-U14)/(MAXA(B12:U12)-MINA
(B13:U13))))<=10%),((((SUM(S14:U14)/3)*6)+((SUM
(R14:U14)/4)+(SUM(Q6:U14)/5)+(SUM(M6:U14)/9)+(SUM
(L6:U14)/10)+(SUM(B14:U14)/20)))/11)

 

[Peo Sjoblom]

Is there a reason for using MAXA/MINA, also you have far too many
parenthesis..

You could for instance name parts of the formula like

=OR(MAXA($B$12:$U$12)-$U$14)/(MAXA($B$12:$U$12)-MINA($B$13:$U$13))>90%,(MAXA
($B$12:$U$12)-$U$14)/(MAXA($B$12:$U$12)-MINA($B$13:$U$13))<=10%)

do insert>name define, let's call it Part1

then you can use

=IF(Part1,(((SUM($S$14:$U$14)/3)*6+(SUM($R$14:$U$14)/4+(SUM($Q$6:$U$14)/5)+(
SUM($M$6:$U$14)/9)+(SUM($L$6:$U$14)/10)+(SUM($B$14:$U$14)/20)))/11))

do the same with the Sum part, call it Part2
then use

=IF(Part1,Part2)

 

[Ken Wright]

OK, one thing you can always do is to take out chunks of it and define it as a
named formula, eg:-

Insert / Name / Define

In the box at the bottom put say

=(MAXA(B12:U12)-U14)/(MAXA(B12:U12)-MINA(B13:U13))

and call it perhaps COND1

You can then replace all instances in your formula of

=(MAXA(B12:U12)-U14)/(MAXA(B12:U12)-MINA(B13:U13))

with COND1 and so on.

VLOOKUP tables are often another way to go, or build bits of the formula into
other cells and then reference the result of those cells.

 

[Guest]

you said there are too many parenthesis. Could you show
me how I can rid them of some of them..

thanks