# Thread: Formula too long (Excel 97 on XP Pro)

1. ## Formula too long (Excel 97 on XP Pro)

Have run into a problem using excel - it is telling me the formula I am using is too long and will not allow additional data to be entered. The excel file is a payroll where worksheets are used to calculate the pay. That information is then transferred to the Master worksheet (gross summary) then certain fields within the worksheets are selected as part of the formula on the master. All worked fine until now when trying to add infomation to the formula due to additional worksheets being added ... the comment formula too long will not allow additional data and therefore unable to get the master sheet to balance.

The formula being used is as follows - =Eyears!C16+Eyears!C19+Eyears!C22+ and picks up data from another 30+ employees. Any help / suggestions would be appreciated!

2. ## Re: Formula too long (Excel 97 on XP Pro)

You could select cells C16, C19, C22 etc. on the Eyears sheet using Click and Ctrl+Click, then give the selection a name using Insert | Name | Define...
Say that you name the selection Employees. You can then use the formula =SUM(Employees)

3. ## Re: Formula too long (Excel 97 on XP Pro)

Thank you for your response while I can partly understand how this works I have run into the problem of how the formula is to progress with adding those same fields for each additional employee. Given that I guess the Name chosen must be unique so as to be able to progress the total on each additional employee.

4. ## Re: Formula too long (Excel 97 on XP Pro)

You could create "partial sums" of cells which do not have formulas that are too long. The have a final formula add up all the partial ones...

Some other options (II am not sure of how your sheets are setup so other things may work better), but perhaps a sumit formula could work or a pivot table.

Perhaps a "redesign" may be needed.

Steve

5. ## Re: Formula too long (Excel 97 on XP Pro)

Did as you suggested with the partial sums and all works well. Thanks

6. ## Re: Formula too long (Excel 97 on XP Pro)

Hi Glen,

From your post, it looks like you need to sum every 3rd row in the range. In that case, you could use:
=SUM(IF(MOD(ROW(Eyears!\$C\$19:\$C\$100)-CELL("Row",Eyears!\$C\$19:\$C\$100),3)=0,Eyears!\$C\$19: \$C\$100,))
as a standard formula to add every 3rd row from row 19 to row 100. Simply change the ranges to suit your needs, and the '3' to change the row frequency.

Cheers

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•