Max number in cell

  • Thread starter Thread starter ALLDREAMS
  • Start date Start date
A

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!
 
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
 
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)
 
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)
 
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.
 
you said there are too many parenthesis. Could you show
me how I can rid them of some of them..

thanks
 
Back
Top