How to get this formula shorter

[ioncila]

Hi

Is there a way to get this formula shorter?

=IF(AG$163=0;0;IF(SUM($AD169:AF169)=$V169;0;IF(AND($V169>SUM($AD169:AF169);AG$163=SUM(AG$168:AI168));0;IF(AND($V169>SUM($AD169:AF169);AG$163>SUM(AG$168:AI168);$V169-SUM($AD169:AD169)>AG$163-SUM(AG$168:AI168));AG$163-SUM(AG$168:AI168);IF(AND($V169>SUM($AD169:AF169);AG$163>SUM(AG$168:AI168);$V169-SUM($AD169:AD169)<=AG$163-SUM(AG$168:AI168));$V169-SUM($AD169:AD169))))))

This Formula fills every cell in range ("AG169:IC227")

Thanks
Ioncila

[SamT]

First, I would name every one of those ranges and refer to them by name.

As to how to make that thing more managable, here's a tutorial I wrote:

http://www.4shared.com/file/C60HcfGn...ojects_v1.html

Basically, it will tell you to break that monster up into segments from last to first, put them into a column with last on top, first on bottom.

Then each segment refers to the cells above it for its comparisons.

Cell("ZZ1") formula

$V169-SUM($AD169:AD169

Cell("ZZ2") formula

SUM(AG$168:AI168)

"ZZ3":=

=AG$163-ZZ2

"ZZ4":=

$V169-SUM($AD169:AD169)

"ZZn":=

=If(ZZ4>ZZ3,ZZ2,ZZ1

In the original Cell you want the results in, just "=ZZn", or name ZZn and use the Range Name.

Of course, the ranges refered to above would be Range Names so you have an idea what is refered to without having to scroll across the sheet to see, meanwhile losing your place in the formula.

The tutorial above also explains how to label the formula cells so it is easy to understand what each is doing, for example, the cell next to "ZZ1" might say "Sum of the Weekly Totals for Division 1." That way, when a formula uses "ZZ1," you can tell that it is using the weekly totals of Dept 1.... No more and

[Bob Phillips]

How about

=IF($V169<=SUM($AD169:AF169),0,
IF(AG$163>SUM(AG$168:AI168),0,
IF($V169-SUM($AD169:AD169)<=AG$163-SUM(AG$168:AI168),$V169-SUM($AD169:AD169),AG$163-SUM(AG$168:AI168))))

[ioncila]

Well, I've been trying the help you all provided to me (thank you very much @SamT and @xld).

However, I think none of purposed solutions do simplify my sheet (or something is missing in my understanding).

In fact, maybe the number of conditions may need that bif and long formula.

I'm attaching the matrix sheet with essential information, just to clearify my issue and if its really possible to optimize those formulas.

Thanks in advance.
Ioncila