LinkBack URL
About LinkBacks
Hi, experts.
I need to automatically calculate relevant prices for all items via formula in the right column of attached example, to match the discounted total from left column.
Prices should be only in hundredths 0.00.
Thanks in advance.
Hi, experts.
I need to automatically calculate relevant prices for all items via formula in the right column of attached example, to match the discounted total from left column.
Prices should be only in hundredths 0.00.
Thanks in advance.
Maybe...
=ROUND(C5*($D$7/($D$6+$D$7)),2)
in I2, copied down
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Copied down this formula shows the price only for the first cell C2.
Replacing C5 with C2- does the job,
Please Login or Register to view this content.
but in this case Total sum is not even close to left column's total.
Last edited by T.I.; 07-22-2018 at 10:19 AM.
Hi again,
I face a very strange situation..
If we look to all 3 columns there is a difference in prices calculation.
Actually the prices are the same but in the middle column (marked with red) not correct.
Could anybody explain me how it happens?
Rounding errors. Expand all to same no of dps...
I see, thank you. Please advise what can be done in this situation? I need prices rounded to 2 decimals only...
You need to use Solver. (a free add-in that might already be installed on your machine).
I know nothing about it, but have spent a little time fiddling with it. I converted all values to cents (to enable me to set "integer" as a constraint for the value of the discounted prices. I got some sort of answer that had a variance of 53 cents.
So, in 15 min, I was able to get something close. Do a bit of reading around....
I will try it. Many thanks for your help!
Running solver from the uploaded file with only two constraints. One that new prices should be equal to or less than the original price and the second that the sum should be equal to the original price - the discount
solver_set.jpg
solver only adjusted the price of item 2 from 1.72 to 1.50 so I think that Glenn's idea with having a min price for each item makes sense.
Alf
Last edited by Alf; 07-23-2018 at 01:52 PM.
Thank you, Alf. I will consider the above.
Thanks for your help, guys!
I used a much laarger number of constraints... But I now see that far fewer can be used. I set them (in my ignorance) one cell at a time. Alf has shown me that you can set constraints to ranges of cells...
With everything priced as cents, they were:
1. Must be an integer.
2. Must be > 0.9 times original price
3. Must be less than 0.95 times the original price.
With a bit of imagination, you'll be able to devise more specific criteria.
Personally I think that using solver is an overkill (even if I do like solver). Simple arithmetic should do it quite easily.
calc_new.jpg
The formula in G4 shows how the discount price is calculated. And the new price multiplied by the appropriate quantity gives the new cost for each item.
Alf
Ps "," is the Scandinavian delimeter
Last edited by Alf; 07-24-2018 at 08:03 AM.
Not sure that I agree entirely... given that the results must be in whole cents....
Hi, Alf.
But in this case we still face a problem with rounding of decimals. All prices have wrong result.
For example in your formula 2300*1.56 = 3597,76. But actually should be 2300*1.56 = 3588.00.
In my documentation I can only use rounding to hundredths...
I found a very primitive solution as a workaround, using ROUNDUP and ROUNDDOWN formulas for each row separately and have difference 0.64 cents
which is acceptable...
Adapting Glenn's idea of setting up price as integers and multiplying them with a 100 + relaxing the lover limit of what the new prices go down to solver found a solution.
How useful that is I don't know as I have no idea of much the prices can be changed.
Alf
Last edited by Alf; 07-25-2018 at 12:10 AM.
Dear User,
Please check if the attached table solve your requirements. Actually the figures you want to round off is actually 1.56424251193749. It may appear as 1.56 but if you round off the value will get changed proportionally.
Thanks, Alf. I will analyse all information you and Glenn gave me in this thread. It seems in my case the solver is the best solution. But first I need to overcome my conservatism and study it thoroughly.Originally Posted by Alf
Thank you, Kamal.Dear User,
Please check if the attached table solve your requirements. Actually the figures you want to round off is actually 1.56424251193749. It may appear as 1.56 but if you round off the value will get changed proportionally.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks