Faster formula?

J

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?
 
H

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?
Click to expand...

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" .
 
J

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
 
H

Harlan Grove

Josh said:
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
Click to expand...
....

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.
 
Top