Excel Formula too long

[ROhara]

I have written a formula that looks in up to 31 tabs (designed to fit a monthly schedule) looking for values. Problem is, by the time I get to the 30th and 31st... the formula is way too long. I can always truncate the formula and put a second portion elsewhere but I would prefer to keep it self contained.

This is a snapshot of the formula used on the 30th. In this case, the 32nd day is called EOM (End of Month). Each time they finish out a month, they will start over on the 1st and EOM will help them maintain the running inventory.

Any suggestions?

=IF(‘Day 30’!B12>0.5,(‘Day 30’!B12),IF(AND(‘Day 30’!B12<0.5,'Day 29'!B12>0.5),('Day 29'!B12),IF(AND(‘Day 30’!B12<0.5,’Day 29’!B12<0.5,'Day 28'!B12>0.5),('Day 28'!B12),IF(AND(‘Day 30’!B12<0.5,’Day 29’!B12<0.5,'Day 28'!B12<0.5,'Day 27'!B12>0.5),('Day 27'!B12),IF(AND(‘Day 30’!B12<0.5,’Day 29’!B12<0.5,'Day 28'!B12<0.5,'Day 27'!B12<0.5,'Day 26'!B12>0.5),('Day 26'!B12),IF(AND(‘Day 30’!B12<0.5,’Day 29’!B12<0.5,'Day 28'!B12<0.5,'Day 27'!B12<0.5,'Day 26'!B12<0.5,'Day 25'!B12>0.5),('Day 25'!B12),IF(AND(‘Day 30’!B12<0.5,’Day 29’!B12<0.5,'Day 28'!B12<0.5,'Day 27'!B12<0.5,'Day 26'!B12<0.5,'Day 25'!B12<0.5,'Day 24'!B12>0.5),('Day 24'!B12),IF(AND(‘Day 30’!B12<0.5,’Day 29’!B12<0.5,'Day 28'!B12<0.5,'Day 27'!B12<0.5,'Day 26'!B12<0.5,'Day 25'!B12<0.5,'Day 24'!B12<0.5,'Day 23'!B12>0.5),('Day 23'!B12),IF(AND(‘Day 30’!B12<0.5,’Day 29’!B12<0.5,'Day 28'!B12<0.5,'Day 27'!B12<0.5,'Day 26'!B12<0.5,'Day 25'!B12<0.5,'Day 24'!B12<0.5,'Day 23'!B12<0.5,'Day 22'!B12>0.5),('Day 22'!B12),IF(AND(‘Day 30’!B12<0.5,’Day 29’!B12<0.5,'Day 28'!B12<0.5,'Day 27'!B12<0.5,'Day 26'!B12<0.5,'Day 25'!B12<0.5,'Day 24'!B12<0.5,'Day 23'!B12<0.5,'Day 22'!B12<0.5,'Day 21'!B12>0.5),('Day 21'!B12),IF(AND(‘Day 30’!B12<0.5,’Day 29’!B12<0.5,'Day 28'!B12<0.5,'Day 27'!B12<0.5,'Day 26'!B12<0.5,'Day 25'!B12<0.5,'Day 24'!B12<0.5,'Day 23'!B12<0.5,'Day 22'!B12<0.5,'Day 21'!B12<0.5,'Day 20'!B12>0.5),('Day 20'!B12),IF(AND(‘Day 30’!B12<0.5,’Day 29’!B12<0.5,'Day 28'!B12<0.5,'Day 27'!B12<0.5,'Day 26'!B12<0.5,'Day 25'!B12<0.5,'Day 24'!B12<0.5,'Day 23'!B12<0.5,'Day 22'!B12<0.5,'Day 21'!B12<0.5,'Day 20'!B12<0.5,'Day 19'!B12>0.5),('Day 19'!B12),IF(AND(‘Day 30’!B12<0.5,’Day 29’!B12<0.5,'Day 28'!B12<0.5,'Day 27'!B12<0.5,'Day 26'!B12<0.5,'Day 25'!B12<0.5,'Day 24'!B12<0.5,'Day 23'!B12<0.5,'Day 22'!B12<0.5,'Day 21'!B12<0.5,'Day 20'!B12<0.5,'Day 19'!B12<0.5,'Day 18'!B12>0.5),('Day 18'!B12),IF(AND(‘Day 30’!B12<0.5,’Day 29’!B12<0.5,'Day 28'!B12<0.5,'Day 27'!B12<0.5,'Day 26'!B12<0.5,'Day 25'!B12<0.5,'Day 24'!B12<0.5,'Day 23'!B12<0.5,'Day 22'!B12<0.5,'Day 21'!B12<0.5,'Day 20'!B12<0.5,'Day 19'!B12<0.5,'Day 18'!B12<0.5,'Day 17'!B12>0.5),('Day 17'!B12),IF(AND(‘Day 30’!B12<0.5,’Day 29’!B12<0.5,'Day 28'!B12<0.5,'Day 27'!B12<0.5,'Day 26'!B12<0.5,'Day 25'!B12<0.5,'Day 24'!B12<0.5,'Day 23'!B12<0.5,'Day 22'!B12<0.5,'Day 21'!B12<0.5,'Day 20'!B12<0.5,'Day 19'!B12<0.5,'Day 18'!B12<0.5,'Day 17'!B12<0.5,'Day 16'!B12>0.5,),('Day 16'!B12),IF(AND(‘Day 30’!B12<0.5,’Day 29’!B12<0.5,'Day 28'!B12<0.5,'Day 27'!B12<0.5,'Day 26'!B12<0.5,'Day 25'!B12<0.5,'Day 24'!B12<0.5,'Day 23'!B12<0.5,'Day 22'!B12<0.5,'Day 21'!B12<0.5,'Day 20'!B12<0.5,'Day 19'!B12<0.5,'Day 18'!B12<0.5,'Day 17'!B12<0.5,'Day 16'!B12<0.5,'Day 15'!B12>0.5),('Day 15'!B12),IF(AND(‘Day 30’!B12<0.5,’Day 29’!B12<0.5,'Day 28'!B12<0.5,'Day 27'!B12<0.5,'Day 26'!B12<0.5,'Day 25'!B12<0.5,'Day 24'!B12<0.5,'Day 23'!B12<0.5,'Day 22'!B12<0.5,'Day 21'!B12<0.5,'Day 20'!B12<0.5,'Day 19'!B12<0.5,'Day 18'!B12<0.5,'Day 17'!B12<0.5,'Day 16'!B12<0.5,'Day 15'!B12<0.5,'Day 14'!B12>0.5),('Day 14'!B12),IF(AND(‘Day 30’!B12<0.5,’Day 29’!B12<0.5,'Day 28'!B12<0.5,'Day 27'!B12<0.5,'Day 26'!B12<0.5,'Day 25'!B12<0.5,'Day 24'!B12<0.5,'Day 23'!B12<0.5,'Day 22'!B12<0.5,'Day 21'!B12<0.5,'Day 20'!B12<0.5,'Day 19'!B12<0.5,'Day 18'!B12<0.5,'Day 17'!B12<0.5,'Day 16'!B12<0.5,'Day 15'!B12<0.5,'Day 14'!B12<0.5,'Day 13'!B12>0.5),('Day 13'!B12),IF(AND(‘Day 30’!B12<0.5,’Day 29’!B12<0.5,'Day 28'!B12<0.5,'Day 27'!B12<0.5,'Day 26'!B12<0.5,'Day 25'!B12<0.5,'Day 24'!B12<0.5,'Day 23'!B12<0.5,'Day 22'!B12<0.5,'Day 21'!B12<0.5,'Day 20'!B12<0.5,'Day 19'!B12<0.5,'Day 18'!B12<0.5,'Day 17'!B12<0.5,'Day 16'!B12<0.5,'Day 15'!B12<0.5,'Day 14'!B12<0.5,'Day 13'!B12<0.5,'Day 12'!B12>0.5),('Day 12'!B12),IF(AND(‘Day 30’!B12<0.5,’Day 29’!B12<0.5,'Day 28'!B12<0.5,'Day 27'!B12<0.5,'Day 26'!B12<0.5,'Day 25'!B12<0.5,'Day 24'!B12<0.5,'Day 23'!B12<0.5,'Day 22'!B12<0.5,'Day 21'!B12<0.5,'Day 20'!B12<0.5,'Day 19'!B12<0.5,'Day 18'!B12<0.5,'Day 17'!B12<0.5,'Day 16'!B12<0.5,'Day 15'!B12<0.5,'Day 14'!B12<0.5,'Day 13'!B12<0.5,'Day 12'!B12<0.5,'Day 11'!B12>0.5),('Day 11'!B12),IF(AND(‘Day 30’!B12<0.5,’Day 29’!B12<0.5,'Day 28'!B12<0.5,'Day 27'!B12<0.5,'Day 26'!B12<0.5,'Day 25'!B12<0.5,'Day 24'!B12<0.5,'Day 23'!B12<0.5,'Day 22'!B12<0.5,'Day 21'!B12<0.5,'Day 20'!B12<0.5,'Day 19'!B12<0.5,'Day 18'!B12<0.5,'Day 17'!B12<0.5,'Day 16'!B12<0.5,'Day 15'!B12<0.5,'Day 14'!B12<0.5,'Day 13'!B12<0.5,'Day 12'!B12<0.5,'Day 11'!B12<0.5,'Day 10'!B12>0.5),('Day 10'!B12),IF(AND(‘Day 30’!B12<0.5,’Day 29’!B12<0.5,'Day 28'!B12<0.5,'Day 27'!B12<0.5,'Day 26'!B12<0.5,'Day 25'!B12<0.5,'Day 24'!B12<0.5,'Day 23'!B12<0.5,'Day 22'!B12<0.5,'Day 21'!B12<0.5,'Day 20'!B12<0.5,'Day 19'!B12<0.5,'Day 18'!B12<0.5,'Day 17'!B12<0.5,'Day 16'!B12<0.5,'Day 15'!B12<0.5,'Day 14'!B12<0.5,'Day 13'!B12<0.5,'Day 12'!B12<0.5,'Day 11'!B12<0.5,'Day 10'!B12<0.5,'Day 9'!B12>0.5),('Day 9'!B12),IF(AND(‘Day 30’!B12<0.5,’Day 29’!B12<0.5,'Day 28'!B12<0.5,'Day 27'!B12<0.5,'Day 26'!B12<0.5,'Day 25'!B12<0.5,'Day 24'!B12<0.5,'Day 23'!B12<0.5,'Day 22'!B12<0.5,'Day 21'!B12<0.5,'Day 20'!B12<0.5,'Day 19'!B12<0.5,'Day 18'!B12<0.5,'Day 17'!B12<0.5,'Day 16'!B12<0.5,'Day 15'!B12<0.5,'Day 14'!B12<0.5,'Day 13'!B12<0.5,'Day 12'!B12<0.5,'Day 11'!B12<0.5,'Day 10'!B12<0.5,'Day 9'!B12<0.5,'Day 8'!B12>0.5),('Day 8'!B12),IF(AND(‘Day 30’!B12<0.5,’Day 29’!B12<0.5,'Day 28'!B12<0.5,'Day 27'!B12<0.5,'Day 26'!B12<0.5,'Day 25'!B12<0.5,'Day 24'!B12<0.5,'Day 23'!B12<0.5,'Day 22'!B12<0.5,'Day 21'!B12<0.5,'Day 20'!B12<0.5,'Day 19'!B12<0.5,'Day 18'!B12<0.5,'Day 17'!B12<0.5,'Day 16'!B12<0.5,'Day 15'!B12<0.5,'Day 14'!B12<0.5,'Day 13'!B12<0.5,'Day 12'!B12<0.5,'Day 11'!B12<0.5,'Day 10'!B12<0.5,'Day 9'!B12<0.5,'Day 8'!B12<0.5,'Day 7'!B12>0.5),('Day 7'!B12),IF(AND(‘Day 30’!B12<0.5,’Day 29’!B12<0.5,'Day 28'!B12<0.5,'Day 27'!B12<0.5,'Day 26'!B12<0.5,'Day 25'!B12<0.5,'Day 24'!B12<0.5,'Day 23'!B12<0.5,'Day 22'!B12<0.5,'Day 21'!B12<0.5,'Day 20'!B12<0.5,'Day 19'!B12<0.5,'Day 18'!B12<0.5,'Day 17'!B12<0.5,'Day 16'!B12<0.5,'Day 15'!B12<0.5,'Day 14'!B12<0.5,'Day 13'!B12<0.5,'Day 12'!B12<0.5,'Day 11'!B12<0.5,'Day 10'!B12<0.5,'Day 9'!B12<0.5,'Day 8'!B12<0.5,'Day 7'!B12<0.5,'Day 6'!B12>0.5),('Day 6'!B12),IF(AND(‘Day 30’!B12<0.5,’Day 29’!B12<0.5,'Day 28'!B12<0.5,'Day 27'!B12<0.5,'Day 26'!B12<0.5,'Day 25'!B12<0.5,'Day 24'!B12<0.5,'Day 23'!B12<0.5,'Day 22'!B12<0.5,'Day 21'!B12<0.5,'Day 20'!B12<0.5,'Day 19'!B12<0.5,'Day 18'!B12<0.5,'Day 17'!B12<0.5,'Day 16'!B12<0.5,'Day 15'!B12<0.5,'Day 14'!B12<0.5,'Day 13'!B12<0.5,'Day 12'!B12<0.5,'Day 11'!B12<0.5,'Day 10'!B12<0.5,'Day 9'!B12<0.5,'Day 8'!B12<0.5,'Day 7'!B12<0.5,'Day 6'!B12<0.5,'Day 5'!B12>0.5),('Day 5'!B12),IF(AND(‘Day 30’!B12<0.5,’Day 29’!B12<0.5,'Day 28'!B12<0.5,'Day 27'!B12<0.5,'Day 26'!B12<0.5,'Day 25'!B12<0.5,'Day 24'!B12<0.5,'Day 23'!B12<0.5,'Day 22'!B12<0.5,'Day 21'!B12<0.5,'Day 20'!B12<0.5,'Day 19'!B12<0.5,'Day 18'!B12<0.5,'Day 17'!B12<0.5,'Day 16'!B12<0.5,'Day 15'!B12<0.5,'Day 14'!B12<0.5,'Day 13'!B12<0.5,'Day 12'!B12<0.5,'Day 11'!B12<0.5,'Day 10'!B12<0.5,'Day 9'!B12<0.5,'Day 8'!B12<0.5,'Day 7'!B12<0.5,'Day 6'!B12<0.5,'Day 5'!B12<0.5,'Day 4'!B12>0.5),('Day 4'!B12),IF(AND(‘Day 30’!B12<0.5,’Day 29’!B12<0.5,'Day 28'!B12<0.5,'Day 27'!B12<0.5,'Day 26'!B12<0.5,'Day 25'!B12<0.5,'Day 24'!B12<0.5,'Day 23'!B12<0.5,'Day 22'!B12<0.5,'Day 21'!B12<0.5,'Day 20'!B12<0.5,'Day 19'!B12<0.5,'Day 18'!B12<0.5,'Day 17'!B12<0.5,'Day 16'!B12<0.5,'Day 15'!B12<0.5,'Day 14'!B12<0.5,'Day 13'!B12<0.5,'Day 12'!B12<0.5,'Day 11'!B12<0.5,'Day 10'!B12<0.5,'Day 9'!B12<0.5,'Day 8'!B12<0.5,'Day 7'!B12<0.5,'Day 6'!B12<0.5,'Day 5'!B12<0.5,'Day 4'!B12<0.5,'Day 3'!B12>0.5),('Day 3'!B12),IF(AND(‘Day 30’!B12<0.5,’Day 29’!B12<0.5,'Day 28'!B12<0.5,'Day 27'!B12<0.5,'Day 26'!B12<0.5,'Day 25'!B12<0.5,'Day 24'!B12<0.5,'Day 23'!B12<0.5,'Day 22'!B12<0.5,'Day 21'!B12<0.5,'Day 20'!B12<0.5,'Day 19'!B12<0.5,'Day 18'!B12<0.5,'Day 17'!B12<0.5,'Day 16'!B12<0.5,'Day 15'!B12<0.5,'Day 14'!B12<0.5,'Day 13'!B12<0.5,'Day 12'!B12<0.5,'Day 11'!B12<0.5,'Day 10'!B12<0.5,'Day 9'!B12<0.5,'Day 8'!B12<0.5,'Day 7'!B12<0.5,'Day 6'!B12<0.5,'Day 5'!B12<0.5,'Day 4'!B12<0.5,'Day 3'!B12<0.5,'Day 2'!B12>0.5),('Day 2'!B12),IF(AND(‘Day 30’!B12<0.5,’Day 29’!B12<0.5,'Day 28'!B12<0.5,'Day 27'!B12<0.5,'Day 26'!B12<0.5,'Day 25'!B12<0.5,'Day 24'!B12<0.5,'Day 23'!B12<0.5,'Day 22'!B12<0.5,'Day 21'!B12<0.5,'Day 20'!B12<0.5,'Day 19'!B12<0.5,'Day 18'!B12<0.5,'Day 17'!B12<0.5,'Day 16'!B12<0.5,'Day 15'!B12<0.5,'Day 14'!B12<0.5,'Day 13'!B12<0.5,'Day 12'!B12<0.5,'Day 11'!B12<0.5,'Day 10'!B12<0.5,'Day 9'!B12<0.5,'Day 8'!B12<0.5,'Day 7'!B12<0.5,'Day 6'!B12<0.5,'Day 5'!B12<0.5,'Day 4'!B12<0.5,'Day 3'!B12<0.5,'Day 2'!B12<0.5,'Day 1'!B12>0.5),('Day 1'!B12),IF(AND(‘Day 30’!B12<0.5,’Day 29’!B12<0.5,'Day 28'!B12<0.5,'Day 27'!B12<0.5,'Day 26'!B12<0.5,'Day 25'!B12<0.5,'Day 24'!B12<0.5,'Day 23'!B12<0.5,'Day 22'!B12<0.5,'Day 21'!B12<0.5,'Day 20'!B12<0.5,'Day 19'!B12<0.5,'Day 18'!B12<0.5,'Day 17'!B12<0.5,'Day 16'!B12<0.5,'Day 15'!B12<0.5,'Day 14'!B12<0.5,'Day 13'!B12<0.5,'Day 12'!B12<0.5,'Day 11'!B12<0.5,'Day 10'!B12<0.5,'Day 9'!B12<0.5,'Day 8'!B12<0.5,'Day 7'!B12<0.5,'Day 6'!B12<0.5,'Day 5'!B12<0.5,'Day 4'!B12<0.5,'Day 3'!B12<0.5,'Day 2'!B12<0.5,'Day 1'!B12<0.5),(EOM!B12),("ERROR")))))))))))))))))))))))))))))))

 

[mikerickson]

Get a helper column on some sheet with formulas like ='Day 1'!B12 and ='Day 2'!B12

Then your formula could refer to that helper column.

 

[cerfani]

You could use the Name manager in the formula tab to replace nested functions with variable names. Your formula could be easier to understand the logic since you can name stuff. For example...

[COLOR=#333333]AND(‘Day 30’!B12<0.5,’Day 29’!B12<0.5,'Day 28'!B12<0.5,'Day 27'!B12<0.5,'Day 26'!B12<0.5,'Day 25'!B12<0.5,'Day 24'!B12<0.5,'Day 23'!B12<0.5,'Day 22'!B12<0.5,'Day 21'!B12<0.5,'Day 20'!B12<0.5,'Day 19'!B12<0.5,'Day 18'!B12<0.5,'Day 17'!B12<0.5,'Day 16'!B12<0.5,'Day 15'!B12<0.5,'Day 14'!B12<0.5,'Day 13'!B12<0.5,'Day 12'!B12<0.5,'Day 11'!B12<0.5,'Day 10'!B12>0.5)[/COLOR]

could be named... IsDayTen (since this is the test you do to return the result from Day 10's tab)

then your formula would read something like =IF(IsDay10,'Day 10'!B12,IF(IsDayNine,'Day 9'!B12,...))

etc

The name manager is in the formulas ribbon tab i believe.

 

[Special-K99]

@ROhara: You might wanna take a close look at your greater than / less than comparisons. There is no catering for equals in any of your formulas.
(Robert Recorde would be turning in his grave!)

 

[mikerickson]

In words, what is that formula looking for. From the first few terms is seems that it is looking at sheets Day 1 through Day 30 in reverse order and returning the value from first sheet whose B12 value is >.5

I should also mention that in the formula
=IF(Sheet1!A1>.5, Sheet1!A1, IF(AND(Sheet1!A1 < .5, Sheet2!A1>.5), Sheet2!A1, "other"))
The test for Sheet1!A1 < .5 is unneeded.

=IF(Sheet1!A1>.5, Sheet1!A1, IF(Sheet2!A1>,5, Sheet2!A1, "other")) would return the same (unless Sheet1 = .5 and Sheet2>.5)