Formula too long

  • Thread starter Thread starter supersub15
  • Start date Start date
S

supersub15

Hi there,

I got a long formula that exceeded the maximum length, so I decided to
divide it in pieces in other cells. It tests if the text in one cell
is "Units1", "Units2", etc., up to 6, then does the calcs.

I split the formula in 6 differents cells:
=SUM(IF(I7="Units1",IF(SUM(((I6+J8)+((I6+J8)*I1))*J7)>1,SUM(((I6+J8)+
((I6+J8)*I1))+1)),SUM(((I6+J8)+((I6+J8)*I1))+((I6+J8)+((I6+J8)*I1))
*J7),Y2))

=SUM(IF(I7="Units2",IF(SUM(((I6+J8)+((I6+J8)*I1))*J7)>0.75,SUM
(((I6+J8)+((I6+J8)*I1))+0.75)),SUM(((I6+J8)+((I6+J8)*I1))+((I6+J8)+
((I6+J8)*I1))*J7),Y3))

etc.

2 problems:
1. The cell is not accepting the argument at the end of the formula
(Y2, Y3, Y4, etc.). Am I using it at the wrong spot?
2. I want to create a cell that tests which Unit I'm using, then refer
to the one of the 6 formulaes (depending if it's Units1, Units2, etc.)

Tanks for the help.
Carlos
 
Hi,

Your Y2, Y3, Y4, etc. are in the right places, but you had a misplaced right bracket. However, you can also considerably shorten the
formulae. For example, instead of:
=SUM(IF(I7="Units1",IF(SUM(((I6+J8)+((I6+J8)*I1))*J7)>1,SUM(((I6+J8)+((I6+J8)*I1))+1)),SUM(((I6+J8)+((I6+J8)*I1))+((I6+J8)+((I6+J8)*I1))*J7),Y2))
which should have been:
=SUM(IF(I7="Units1",IF(SUM(((I6+J8)+((I6+J8)*I1))*J7)>1,SUM(((I6+J8)+((I6+J8)*I1))+1),SUM(((I6+J8)+((I6+J8)*I1))+((I6+J8)+((I6+J8)*I1))*J7)),Y2))
you can use:
=IF(I7="Units1",IF((I6+J8)*(I1+1)*J7>1,(I6+J8)*(I1+1)+1,(I6+J8)*(I1+1)*(J7+1)),Y2)
 
Hi Robert,

True, but I wanted to show the OP how to get the same functionality in a shorter formula without introducing anything new.
 
If shortening the formula on the worksheet was all that mattered, one could simply define all or part it as a Name and insert the
defined name(s) wherever the result is needed. For example (for just parts of the formula):
=IF(I7="Units1",IF(REF,TRU,FALS),Y2)
 
Back
Top