You cant't write a formula that returns an empty value. It would erase itself, so to say.
But you can write a formula that return some fixed value (like "empty") if another cell is empty (=if(isempty(a1),"empty",a1+1) and you can write a macro that loops trough all cells and clears all that contain the value "empty". But it clears the formula also, so the macro should start with making a copy of the sheet and continue working in that copy.
I have a set of data, with a different number of data points in each column. I want to transform each data point by dividing the data by the background levels - easy enough, i'm using =(B6/$C$3), where C3 contains the background.
I've then dragged the selection to encompass all possible cells, but as there are different numbers of data points in each column, many of the cells return 0.00 with an error "refers to empty cell." This will then heavily impact on further analysis (averages etc), and i can't simply use an if function as 0 is a valid data point.
Basically, is there a way of getting excel to return an empty cell if the formula inputted refers to an empty cell?