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

Almost works see test values below

your suggested formula gives the value of 15 (although i suspect that is
coming from being the maximum between the goal (15) and the rounded up value
(13.26))

using 13 the final total with the 10% deduction comes to 14.04 = too low

using 14 the final total with deduction comes to 15.12 = this is what i need

using the 15 from your formula the final total is 16.20 = a bit too high

does that help illustrate the problem?

Thanks for the help so far,

G Baker

"Mike H" wrote:

Gavin

Sorry missed the second bit. That precisely why I made the comment before
that in practice I'd have these values in cells and reference them and not as
part of the formula. If you then want to change the discount from 10% (*0.9)
to 15% (*0.85) then changing a single cell will update multiple formula.

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