excluding cells from calculations by "marking" the cells?

L

lossinc

Within a range of cells designated for a calculation, I am tyring t
exclude certain cells without having to modify my function (usuall
very complicated) or using conditional formatting.

Example:
- 10 data values, function calculates result
- 2 of the data values (never the same two) are unacceptable

I want these two values to be excluded from the caculation. What I nee
is a way of designating these cells as "Ignore". I don't want to delet
the values, nor do I want to cut out the cells.

Any ideas? Thanks
 
A

Andy B

Hi

Are these 'unacceptable' cells classed as such because they are the
lowest/smallest/highest/biggest? Is there a way of determining which they
are using a formula/function?
 
F

Frank Kabel

Hi
one way: assigne a name for the used cells and use this
name within your calculation.
But you may post your actual formula for a more specific
answer
 
L

lossinc

Thanks for the answers! Regarding your questions:

Frank: I am working with a template workbook into which I paste m
data. Every time I do an experiment the data are different, an
sometimes the calculations reveal that the results are flawed, hence
would like to exclude the cells (in way "ignore the cells"). Since th
data always change, I can't predict where the "bad" value will appear
thus assigning a name for the cells to be taken out is impossibl
beforehand. I could, of course, do it afterwards, but that defeats th
purpose.

Andy: The criteria for excluding the cell vary from case to case.
have to monitor a number of variables, and if any one of them is no
acceptable, the value to which the variable pertains has to be take
out. The criteria are never extrema, but more like "... if A exceeds
or if B is an "outlier"...". It's too complicated to actually automat
the exclusion of these bad data, so I would merely like to mark the
manually after a visual inspection (which I currently enhance vi
conditional formatting) of my data set, such that the "bad" values ar
ignored in subsequent calculations.

Added note: If you have the time, send me private message and I wil
attach the workbook to the repsonse. I am new to the forum as you ma
have noted, and I haven't figured out how to send you guys emails, yet
I personally have deliberately submitted a false email address... onc
bitten twice shy... last time I submitted my address to a web site tha
promised not to pass is on, it resulted in me getting 1000+ spam mail
a week
 
A

AlfD

Hi!

Since you have to identify the "bad" data by inspection, maybe put
marker in a spare/new column (say, D) as you spot the unwanted rows?

Then you could rework the calculations to ignore these
(=if(D1="#","",[existing formula]) so putting a blank instead of
value.

Alternatively, create a new, temporary worksheet without the marke
items. A quick way would be to copy the worksheet; sort by the colum
with # in it. Delete all the items with # in that column. Most of thi
could be automated with nothing more complicated than a recorde
macro.

Al
 
L

lossinc

Thanks for the advice AlfD,
I may be able to work on that marker/sorting/macro idea of yours. Th
problem is that the decision I have to automate is not "Calculat
[function] of the data... yes/no?", but "Include this data point in th
caculation with [function]... yes/no?". The question is not, whethe
to calculate with the entire data set, but what data points to includ
in the calculation.

Anyway, I like your idea and I will try to work something out with it.

Regards
 
A

AlfD

Hi!

Of course, if you want to include rather than exclude points, you ca
just turn the IF formula round - put <>"#" instead of ="#".

Al
 
Top