View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.misc
Mike H Mike H is offline
external usenet poster
 
Posts: 11,501
Default reverse percentage calculation

Hi,

That seems to work, could you explain where the 0.9 comes from


Multiplying by .9 subtracts the 10% you required.

Mike

"Gavin Baker" wrote:

That seems to work, could you explain where the 0.9 comes from at the end of
the formula? so that I can reference it to the relevant cells.

Also would it make any difference if the percentage changed for different
line, eg some at 10% some at 15% etc.

Many thanks,

G Baker

"Mike H" wrote:

Try this


=MAX(15,(ROUNDUP(15/1.2,0)*1.2*0.9))


In practice I'd have all these values in cells an reference the cells in the
formula.

Mike

"Gavin Baker" wrote:

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