Putting a cap on a figure entered in a cell

**Illustris13** · 03-29-2013, 04:37 AM

I have a cell where I want a ceiling placed on the figure which can be entered (say 14000). If any larger figure of 14001 upwards is entered I would like a statement to display in the cell such as 'not applicable'. Any ideas please?

This cell (we can call it A1) is also linked to other cells with formula attached. One of the other cells (A2) contains a formula. Is there any way that I can add something to the formula already in place that will result in the figure in A1 being ignored if 14001 or more is entered - the formula currently used in A2 is
=6000/(A1-B1)

Any help would be gratefully received!

**Marcol** · 03-29-2013, 04:43 AM

Have a look at Data Validation for A1

Allow Whole Number / Decimal, max/min limits.

**Fotis1991** · 03-29-2013, 04:56 AM

Perhaps.??

=if(a1>14001,"",6000/(A1-B1))

**Illustris13** · 04-05-2013, 03:18 PM

Thanks for the advice, data validation appears to be the answer.
The formula keeps returning a 'circular reference error' message. Perhaps I didn't explain properly - I just need to stop any figure above 14000 being entered in A1 and other cells that use cell A1 in effect 'feed' off the number that is entered. Sorry but my experience of excel is very limited, but you guys are a great help!