• Status: Solved
• Priority: Medium
• Security: Public
• Views: 454

Validate Percentage Discount with Rounded Values

Hi experts,

Maybe this sounds as a simple question, but a didn't realize eficient way of doing this.

I have a function that needs to validade a discount(%) applied over a product price. Basically, this functions returns false if the discount is greater then the max alowed value.

For example:

Product Price: 10.00
Max Discount: 5%

So, following these parameters, all values greater then 9.50 will be allowed. Other values will not.
Note that until now I didn't applied no kind of rounding.

The problem started when I applied some rounding on calculations.

Suppose that, over a certain price, I discounted 10% and the returned price is 8.87443. If I validate this value on the previous function, the system will check that the discount is 10% and the value will be validated.

But, for some reasons, I need to round this value to 2 decimal places. So, 8.87443 will be changed to 8.87. Submiting this rounded value to the same function will return false becaue the calcutaled discount % will be greater then 10. (10.034, by eg. I didn't calculated the values).

There is some aproaches to apply, like rounding the calculated discount % on the validation function, but, after some tests, I realized that simlple rounding the calculated % will not always work, specially on smaller prices (like 1.00, 0.60 and so on). By eg, a 10.3% of discount on a product that costs 0.78 cents is acceptable, but 10.3% dilscount on a product that costs 10,000.00 are not.

So, I would like to know if anybody has another technique that considers something like rounded decimal places and the original price, that will be able to validate the discount calculated after rounding, if greater than the limit, is acceptable or not.

Thanks a lot for any advice.
0
regisdaniel
• 7
• 2
• 2
• +4
2 Solutions

Solutions ConsultantCommented:
Could you please supply the function and some dummy data so I can see what is happening

Michael
0

Commented:
Hello regisdaniel,

I am not sure why you have to round the value before passing it to your formula.  If you want your report looks clean with only 2 decimal places while still keeping its exact number, you can use Format Cells - Number feature of Excel.

I hope this helps.

Long
0

Commented:
Use floor to compare. Something like =if(value < floor(minPrice, 3), false, true)
0

Commented:
Sorry, floor(minPrice, .01)
0

Commented:
Instead of calculating the %discount, you can compute the minimum price then round it to 2 decimal places and compare that value to the validated price.

e.g.

Product Price: 31.30
Max Discount: 0.88
Minimum Price: 27.544
Rounded Min Price: 27.54 <- Return true if greater or equal to this price
0

Commented:
Drat. Actually, you should use ceiling.
Let's say the price is \$8.00 an the max discound is 7%, then anything < ceiling(.93*8, .01) is going to round to below the value and should be false.
0

Commented:
>>Max Discount: 0.88
I meant 12% discount
0

Author Commented:
Hi Michael, thanks for your response.

I created a Excel file that shows the problem. You can download it here: http://db.tt/nJPL4af
It is hosted on my Dropbox public folder. Since I'm using the mobile version of EE, I cant attach the file here.

The last column implements the same logic used to my function. It simples check to see if the calculated discount is greater then the desired max discount, if it is, is return false, otherwise, true.

The function should implement a logic that detects that the diference is caused by rounding and then, return true. As I said before: 10% discount over 0.70 is 0.63; 10.5% over 0.70, when rounded, is also 0.63.

Thanks
0

Commented:
=IF(D2<ROUND(A2*(1-B2),2),"no","yes")
0

Commented:
So you calculate the minimum possible rounded price and compare against it.
D2 is the price in question, A2 is the original price, B2 is the max discount.
0

Author Commented:
Thomasian, your sugestion should work. I'll try it here.

Tommy, will your solution work with greater values, like \$10.000,00? I'll check it also.

Thanks!
0

Commented:
Sure will. It's guaranteed since it just tests the same thing you are using as the gold standard.
0

Commented:
"But, for some reasons, I need to round this value to 2 decimal places. So, 8.87443 will be changed to 8.87. Submiting this rounded value to the same function will return false becaue the calcutaled discount % will be greater then 10. (10.034, by eg. I didn't calculated the values)."
-
Note \$8.87 is indeed more than 10% below the base price so it should be rejected. It was. there is a problem?
0

progCommented:
knock the maximum percentage off the price, round it, then that is always the minimum price

simples
0

Commented:
deighton, I agree. That is exaclty what my formula does.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.