View Single Post
  #8   Report Post  
Posted to microsoft.public.excel.misc
Gavin Baker Gavin Baker is offline
external usenet poster
 
Posts: 5
Default reverse percentage calculation

am I correct in thinking this will give the minimum of either the deduction
"1.56" or the difference between total and goal "0.6"?

figures are from current example values.

these values are already calculated as I thought they may be needed in some
other calculation later.

thanks for the advice.

"David Biddulph" wrote:

If your goal of 15 is in A1, and your 15.60 is in F1, then your deduction
will be =MIN(F1*10%,F1-A1)

For any reasonably large starting point, of course, you won't get near the
10% figure, because all you are deducting is the effect of the whole number
rounding between your division by 1.2 and the multiplying back up by 1.2.
You'd only get to 10% if your starting number were small so that the whole
number rounding between those steps becomes more significant.
--
David Biddulph

"Gavin Baker" wrote in message
...
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 a product which when 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 value.

I can calculate the amount with 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 multiplier to get the total

13.00 * 1.20 = 15.60 (which is above the goal of 15)


Now 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 this 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.

Hope the above can be understood by someone as it sure confuses me...

Many thanks,

G Baker