View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.misc
David Biddulph[_2_] David Biddulph[_2_] is offline
external usenet poster
 
Posts: 8,651
Default reverse percentage calculation

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