Solved

Validate Percentage Discount with Rounded Values

Posted on 2011-03-03
15
429 Views
Last Modified: 2012-05-11
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
Comment
Question by:regisdaniel
  • 7
  • 2
  • 2
  • +4
15 Comments
 
LVL 23

Expert Comment

by:Michael74
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

by:longtruong
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

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

Expert Comment

by:TommySzalapski
ID: 35033489
Sorry, floor(minPrice, .01)
0
 
LVL 22

Assisted Solution

by:Thomasian
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

by:TommySzalapski
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

by:Thomasian
ID: 35033507
>>Max Discount: 0.88
I meant 12% discount
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:regisdaniel
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

by:
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

by:TommySzalapski
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

by:regisdaniel
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

by:TommySzalapski
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

by:aburr
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

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

simples
0
 
LVL 37

Expert Comment

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

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Article by: Nadia
Linear search (searching each index in an array one by one) works almost everywhere but it is not optimal in many cases. Let's assume, we have a book which has 42949672960 pages. We also have a table of contents. Now we want to read the content on p…
Lithium-ion batteries area cornerstone of today's portable electronic devices, and even though they are relied upon heavily, their chemistry and origin are not of common knowledge. This article is about a device on which every smartphone, laptop, an…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

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

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

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now