IF (only that function would work)

  • Thread starter Thread starter LochLomondMan
  • Start date Start date
L

LochLomondMan

I only have this problem with IF

I am scoring data and want Excel to put an 'X' in one of four cells,
A,B,C, or D - depending upon the score.

I get the same problem on a single IF as I do in a nested.

I started a new trial sheet to prove my formula, I created a range of
values in column a then my four columns A-D.

My formula in column b 'A' is:

=IF($A3>50,"error",IF($A3>34,"X",IF($A3>25,"",IF($A3>10,"",IF($A3>0,"","n/a")))))

in column c 'B':

=IF($A3>34,"",IF($A3>25,"X",IF($A3>10,"",IF($A3>0,"","n/a"))))

etc..

All worked fine. I copied the formulas into working sheet & tried to
change 'A3' to 'G3'. Every time I get "error in formula". I solved it
by adding extra columns in my trial sheet to make A3 into G3 - it
worked. But either in the trial sheet or my working sheet if I try to
make any change to the formula it returns an error.

I don't have problems with other formulas.

Problem exists even if I use 'insert formula'.

Problem exists on other PCs (all Excel 2000).

Can anyone help? Please....
 
Hi


You can simplify your formulas:
=IF($A3="","n/a",IF($A3>50,"Error",IF(A3>34,"X","")))
=IF($A3="","n/a",IF($A3>34,"",IF(A3>25,"X","")))
=IF($A3="","n/a",IF($A3>25,"",IF(A3>10,"X","")))
=IF($A3="","n/a",IF($A3>10,"","X"))

About moving:
Muve the cell(s) on trial sheet - before copying formula(s). Select cell
A3/Column A. Poit with mouse to border of selection, until an arrow is
displayed, and press left taste down. Holding the left mouse-taste down,
drag the selection to new destination - all formulas linked to this
selection are changed accordingly.


Arvi Laanemets
 
Back
Top