- Joined
- Jan 29, 2009
- Messages
- 2
- Reaction score
- 0
I have encountered a strange problem which I can't get my head around, will
try to explain as best I can.
I am trying to calculate the value of an item after being multiplied by a
figure, will then have 10% deducted from the total leaving a final total.
However the final total has to be above a minimum goal value.
I can calculate the amount without the percentage deduction getting involved but I am unable to wrap my brain around getting the percentage involved.
eg. Goal is 15, and multiplier is 1.20
so the amount should be 15 / 1.20 = 12.50 (so far so easy)
this 12.50 is then rounded up to nearest whole number becoming 13.00
now this rounded up number is multiplied by the multiplier to get the total
13.00 * 1.20 = 15.60 (which is above the goal of 15)
This is the difficult bit, there needs to be a 10% deduction from the total
10% of 15.60 = 1.56 to deduct
final total = 14.04 (this is now under the goal amount)
My question is this, can the above be calculated in a formula to work out the 10% deduction and if that takes the final total below the goal, can I then work out what the amount should be to keep it above the goal.
I think this is a reverse percentage removal problem.
Using 13 gives 14.04 as the final result (below the goal)
14 gives 15.12 (perfect)
15 gives 16.20 (above goal, not ideal)
The formula can be broken down into as many parts as necessary, providing it gives the correct result.
Ultimately this will be set into a spreadsheet for automatic calculation, I don’t want the spreadsheet formula just the plain maths calculations please…
Hope the above can be understood by someone as it sure confuses me...
Many thanks,
G Baker
try to explain as best I can.
I am trying to calculate the value of an item after being multiplied by a
figure, will then have 10% deducted from the total leaving a final total.
However the final total has to be above a minimum goal value.
I can calculate the amount without the percentage deduction getting involved but I am unable to wrap my brain around getting the percentage involved.
eg. Goal is 15, and multiplier is 1.20
so the amount should be 15 / 1.20 = 12.50 (so far so easy)
this 12.50 is then rounded up to nearest whole number becoming 13.00
now this rounded up number is multiplied by the multiplier to get the total
13.00 * 1.20 = 15.60 (which is above the goal of 15)
This is the difficult bit, there needs to be a 10% deduction from the total
10% of 15.60 = 1.56 to deduct
final total = 14.04 (this is now under the goal amount)
My question is this, can the above be calculated in a formula to work out the 10% deduction and if that takes the final total below the goal, can I then work out what the amount should be to keep it above the goal.
I think this is a reverse percentage removal problem.
Using 13 gives 14.04 as the final result (below the goal)
14 gives 15.12 (perfect)
15 gives 16.20 (above goal, not ideal)
The formula can be broken down into as many parts as necessary, providing it gives the correct result.
Ultimately this will be set into a spreadsheet for automatic calculation, I don’t want the spreadsheet formula just the plain maths calculations please…
Hope the above can be understood by someone as it sure confuses me...
Many thanks,
G Baker