I have this formula that I wrote years ago that does a counting process. It is so big though and is used for 4 columns and 3051 rows, on 12 different sheets. It makes my book over 800mb and although it does calculate fine, it takes forever to open, link, and update. All it does is it checks the data in say row 3050 against row 3051 and if it's smaller or larger, will give a result of 1. If row 3050 and 3051 is the same, it will check row 3050 against row 3051 and row 3052 and will do so repeatedly until it finds a smaller or larger value. This formula will check if say 3050 is greater than say 3051 and so on. I also have one that does the same thing except it checks for if 3050 is less than 3051.
There has to be a better way to achieve this that will cut down on the size of the book, make it run cleaner and faster. Is there a better way than what I have come up with?
This is the formula:
=IF(Link!B2="?","",IF(Link!B2>Link!B3,1,IF(AND(Link!B2=Link!B3,Link!B2>Link!B4),1,IF(AND(Link!B2=Link!B3,Link!B2=Link!B4,Link!B2>Link!B5),1,IF(AND(Link!B2=Link!B3,Link!B2=Link!B4,Link!B2=Link!B5,Link!B2>Link!B6),1,IF(AND(Link!B2=Link!B3,Link!B2=Link!B4,Link!B2=Link!B5,Link!B2=Link!B6,Link!B2>Link!B7),1,IF(AND(Link!B2=Link!B3,Link!B2=Link!B4,Link!B2=Link!B5,Link!B2=Link!B6,Link!B2=Link!B7,Link!B2>Link!B8),1,IF(AND(Link!B2=Link!B3,Link!B2=Link!B4,Link!B2=Link!B5,Link!B2=Link!B6,Link!B2=Link!B7,Link!B2=Link!B8,Link!B2>Link!B9),1,IF(AND(Link!B2=Link!B3,Link!B2=Link!B4,Link!B2=Link!B5,Link!B2=Link!B6,Link!B2=Link!B7,Link!B2=Link!B8,Link!B2=Link!B9,Link!B2>Link!B10),1,IF(AND(Link!B2=Link!B3,Link!B2=Link!B4,Link!B2=Link!B5,Link!B2=Link!B6,Link!B2=Link!B7,Link!B2=Link!B8,Link!B2=Link!B9,Link!B2=Link!B10,Link!B2>Link!B11),1,IF(AND(Link!B2=Link!B3,Link!B2=Link!B4,Link!B2=Link!B5,Link!B2=Link!B6,Link!B2=Link!B7,Link!B2=Link!B8,Link!B2=Link!B9,Link!B2=Link!B10,Link!B2=Link!B11,Link!B2>Link!B12),1,IF(AND(Link!B2=Link!B3,Link!B2=Link!B4,Link!B2=Link!B5,Link!B2=Link!B6,Link!B2=Link!B7,Link!B2=Link!B8,Link!B2=Link!B9,Link!B2=Link!B10,Link!B2=Link!B11,Link!B2=Link!B12,Link!B2>Link!B13),1,IF(AND(Link!B2=Link!B3,Link!B2=Link!B4,Link!B2=Link!B5,Link!B2=Link!B6,Link!B2=Link!B7,Link!B2=Link!B8,Link!B2=Link!B9,Link!B2=Link!B10,Link!B2=Link!B11,Link!B2=Link!B12,Link!B2=Link!B13,Link!B2>Link!B14),1,IF(AND(Link!B2=Link!B3,Link!B2=Link!B4,Link!B2=Link!B5,Link!B2=Link!B6,Link!B2=Link!B7,Link!B2=Link!B8,Link!B2=Link!B9,Link!B2=Link!B10,Link!B2=Link!B11,Link!B2=Link!B12,Link!B2=Link!B13,Link!B2=Link!B14,Link!B2>Link!B15),1,IF(AND(Link!B2=Link!B3,Link!B2=Link!B4,Link!B2=Link!B5,Link!B2=Link!B6,Link!B2=Link!B7,Link!B2=Link!B8,Link!B2=Link!B9,Link!B2=Link!B10,Link!B2=Link!B11,Link!B2=Link!B12,Link!B2=Link!B13,Link!B2=Link!B14,Link!B2=Link!B15,Link!B2>Link!B16),1,IF(AND(Link!B2=Link!B3,Link!B2=Link!B4,Link!B2=Link!B5,Link!B2=Link!B6,Link!B2=Link!B7,Link!B2=Link!B8,Link!B2=Link!B9,Link!B2=Link!B10,Link!B2=Link!B11,Link!B2=Link!B12,Link!B2=Link!B13,Link!B2=Link!B14,Link!B2=Link!B15,Link!B2=Link!B16,Link!B2>Link!B17),1,""))))))))))))))))
I really don't know what would be the best way to do this, and if it's something I need to post in the commercial services, that would be fine with me. I'd just really like to get this fixed so it will run faster.
Thanks so much for looking at this!!!
Bookmarks