How to write excel formula for data validation between range of numbers?

Question

I am not familiar with much excel Syntax and features. But I am trying to add data validations for few cells. So, for a cell if the value entered is between 0 - 100, it should allow to enter the number, if its not between 0-100 it should enter No.

Basically if the entered value is not a numeric number it should allow them to write NO

SO far i managed to get this logic: Lets say I am using cell A1 IF(AND(A1>0,A1<100),"YES","NO")

The problem with this logic is If the condition is satisfied , this will write yes but I want them to write the number, not YES.

Please ask if this doesn't make much sense?Thanks :)

A guess: =OR(AND(A1>0,A1<100),A1="NO").

BigBen
– BigBen

2022-03-31 13:14:23 +00:00
Commented Mar 31, 2022 at 13:14 — BigBen
– BigBen, Commented Mar 31, 2022 at 13:14

Solar Mike · Accepted Answer · 2022-03-31 13:47:51Z

1

So using Data Validation:

Tested with wrong numbers:

And you can put the lim its on the sheet as so:

BUT has to have absolute referencing to cells C2 & C3

answered Mar 31, 2022 at 13:47

Solar Mike

8,4255 gold badges23 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to write excel formula for data validation between range of numbers?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related