Excel Validation rule to prevent cell from going below a certain percentage based on value of another cell.

Ironman317

New Member
Joined
Oct 4, 2016
Messages
2
Hello!

I'm trying to create a quote template in Excel.

The issue happens when sales wants to give a discount, but then give too big of a discount on the price.

To solve this issue we've duplicated the price in another hidden cell and reference that cell in the validation rule. The idea is that they wouldn't be able to go below 10% of what the original price is.

Currently the validation rule that we are running is as follows...

-----------------------------------------------------------------------

Allow: (Decimal) ignore blank = true

Data: Greater than or Equal to

Minimum: =A1 - (A1*.1) - A1 is the hidden field holding the original cost value. It's saying that the minimum discount you can give is based on original price subtracted by 10% of the original price.

---------------------------------------------------------------------

The validation rule is not triggering when you put values into them, so I'm wondering what I might be missing here. Any help would be greatly appreciated!
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,751
Welcome to the forum.

That works when I try it. Make sure the minimum formula actually points to the right cell. If it's on another sheet, use the sheet reference:

=Sheet2!A1-(Sheet2!A1*.1)

or better:

=Sheet2!A1*.9
 

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
Hi, welcome to the board.

I think you're using terms like discount and price a little loosely.
Let's try and be absolutely clear about what you mean here.

If A1 contains the value $100, are you saying you don't want the sales people to go below $90 ?

If so, maybe something like this.
Assuming you are putting the prices into cell A2...

Allow: Custom
Formula: =A2>(A1*.9)

This will mean that the lowest price they can input is $90.
 

Ironman317

New Member
Joined
Oct 4, 2016
Messages
2
Eric and Gerald, thanks so much. I got the rule working.

I believe it was the way I wrote the formula before that was causing the issue. When I entered "=Sheet2!A1*.9" the validation rule worked.

Glad that this forum exists!


 

Watch MrExcel Video

Forum statistics

Threads
1,099,166
Messages
5,467,021
Members
406,518
Latest member
SilverSixx

This Week's Hot Topics

Top