Faster formula?

[Josh]

I have a spreadsheet of 25 columns & 5000 rows & growing.

3 columns contain the following formulas:
=IF(ISBLANK(M2),"NOT SHIPPED",IF(M2=E2,"ON TIME",IF(M2<E2,"EARLY","LATE")))

=IF(ISBLANK(M2)," ",IF(M2=E2,"0",IF(M2>E2,(M2-E2),(E2-M2))))

=IF((ISBLANK(E2)),"NO DUE DATE",(E2-A2))

Is there a better way to write these that may speed things up?

 

[Harlan Grove]

I have a spreadsheet of 25 columns & 5000 rows & growing.

3 columns contain the following formulas:
=IF(ISBLANK(M2),"NOT SHIPPED",IF(M2=E2,"ON TIME",IF(M2<E2,"EARLY","LATE")))

=IF(ISBLANK(M2)," ",IF(M2=E2,"0",IF(M2>E2,(M2-E2),(E2-M2))))

=IF((ISBLANK(E2)),"NO DUE DATE",(E2-A2))

Is there a better way to write these that may speed things up?

Maybe. Depends on how cute you want to be. Very cute:

First formula:
=IF(ISNUMBER(M2),M2-E2)

with custom numeric format "LATE";"EARLY";"ON TIME";"NOT SHIPPED" .

Second formula:
=IF(ISNUMBER(M2),ABS(M2-E2))

with custom numeric format 0;"ERROR!";0; . The ERROR! for negatives is
appropriate since the formula should NEVER return a negative value. If it does,
you'd have big problems (as would Excel's ABS function).

Third formula:
=IF(ISNUMBER(E2),E2-A2)

with custom numeric format 0;-0;0;"NO DUE DATE" .

 

[Jack Sons]

Harlan,

Really amazing, compliments.

Jack Sons.

 

[Josh]

Thanks Harlan,

You have good mojo

That seems to help some, but things are still a little slow and this
spreadsheet will probably triple in length over the next 6 months.

Any other suggestions?

I am trying to convert to access but not making much headway


Josh

 

[Harlan Grove]

That seems to help some, but things are still a little slow and this
spreadsheet will probably triple in length over the next 6 months.

Any other suggestions?

I am trying to convert to access but not making much headway

....

No other suggestions. If you're headed to Access, that's the right way to
go. You'll need to play around with creating indexed fields. Indexed
database processing is the best you can do short of writing your own code to
process your data.