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

[Ironman317]

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!

 

[Eric W]

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]

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]

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!

 

[Eric W]

Glad we could help!