Solved

Validate Percentage Discount with Rounded Values

Posted on 2011-03-03
441 Views
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
Question by:regisdaniel
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
• 7
• 2
• 2
• +4

LVL 23

Expert Comment

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

Michael
0

LVL 3

Expert Comment

ID: 35033442
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

LVL 37

Expert Comment

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

LVL 37

Expert Comment

ID: 35033489
Sorry, floor(minPrice, .01)
0

LVL 22

Assisted Solution

Thomasian earned 250 total points
ID: 35033502
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

LVL 37

Expert Comment

ID: 35033505
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

LVL 22

Expert Comment

ID: 35033507
>>Max Discount: 0.88
I meant 12% discount
0

Author Comment

ID: 35033540
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

LVL 37

Accepted Solution

TommySzalapski earned 250 total points
ID: 35033565
If your spreadsheet, use a formula like this
=IF(D2<ROUND(A2*(1-B2),2),"no","yes")
0

LVL 37

Expert Comment

ID: 35033568
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 Comment

ID: 35033581
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

LVL 37

Expert Comment

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

LVL 27

Expert Comment

ID: 35034080
"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

LVL 18

Expert Comment

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

simples
0

LVL 37

Expert Comment

ID: 35038680
deighton, I agree. That is exaclty what my formula does.
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calculâ€¦
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to usâ€¦
Suggested Courses
Course of the Month4 days, 14 hours left to enroll

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.