rajiv@ee.rochester.edu (Rajiv Arora) (03/21/91)
I have a problem in Excel 2.2 that I'm sure has a simple answer. I'm creating a link between two cells in different worksheets. The problem I'm having is that when I leave my source cell blank (indicating *missing value* rather than 0), my destination cell always gets the value zero. This is mucking up my calculations (average and standard deviation). My question: is there any way to suppress the link returning a 0 intead of a missing value? I did RTFM but couldn't find any thing about it. (Which doesn't mean it's not there, in bold face, staring me straight in the face!) -Rajiv Arora -- Addresses: UUCP: ...!rochester!ur-valhalla!rajiv Internet: rajiv@ee.rochester.edu
robg@Apple.COM (Rob Griffiths) (03/22/91)
In article <1991Mar21.150720.9313@ee.rochester.edu> rajiv@ee.rochester.edu (Rajiv Arora) writes: > >My question: is there any way to suppress the link returning a 0 intead of a >missing value? I've got the "brute force, complex formula" solution. Maybe someone has a more elegant method, but here's mine. Replace the basic formula with the following: =IF(ISBLANK(Worksheet2!$A$1),"",Worksheet2!$A$1) Of course, in this example, Worksheet2 is the linked worksheet, and I'm referencing cell $A$1. Change to suit your requirements. ;). Warning: This could get very memory-intensive if you have >lots< of links that you need to check for zero/blank status. However, it does work, and will correctly treat a zero and a blank differently when performing averages and the like. -rob.