A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

reverse percentage calculation



 
 
Thread Tools Display Modes
  #1  
Old January 29th 09, 12:29 PM posted to microsoft.public.excel.misc
Gavin Baker
external usenet poster
 
Posts: 5
Default reverse percentage calculation

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
Ads
  #2  
Old January 29th 09, 12:44 PM posted to microsoft.public.excel.misc
Mike H
external usenet poster
 
Posts: 11,501
Default reverse percentage calculation

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

  #3  
Old January 29th 09, 01:04 PM posted to microsoft.public.excel.misc
David Biddulph[_2_]
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



  #4  
Old January 29th 09, 01:13 PM posted to microsoft.public.excel.misc
Gavin Baker
external usenet poster
 
Posts: 5
Default reverse percentage calculation

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

  #5  
Old January 29th 09, 01:24 PM posted to microsoft.public.excel.misc
Mike H
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

  #6  
Old January 29th 09, 01:26 PM posted to microsoft.public.excel.misc
Mike H
external usenet poster
 
Posts: 11,501
Default reverse percentage calculation

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

  #7  
Old January 29th 09, 01:44 PM posted to microsoft.public.excel.misc
Gavin Baker
external usenet poster
 
Posts: 5
Default reverse percentage calculation

thanks, i have a calculation as follows to work out the "*0.9" value
(1-(1*percentage))

so for 10% = 0.9 15% = 0.85

testing the rest out right now


"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

  #8  
Old January 29th 09, 01:47 PM posted to microsoft.public.excel.misc
Gavin Baker
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

>
>
>

  #9  
Old January 29th 09, 02:09 PM posted to microsoft.public.excel.misc
Gavin Baker
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

  #10  
Old January 29th 09, 03:01 PM posted to microsoft.public.excel.misc
David Biddulph[_2_]
external usenet poster
 
Posts: 8,651
Default reverse percentage calculation

Yes, if you've already calculated your 1.56 and 0.6, the MIN function will
give the minimum of those two.

MIN is a standard Excel function, so its syntax (and examples of the use)
can be seen in Excel help.
--
David Biddulph

"Gavin Baker" > wrote in message
...
> 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

>>
>>
>>



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Percentage calculation mmacnz Excel Discussion (Misc queries) 3 October 22nd 07 05:54 AM
percentage calculation Gabriel Excel Discussion (Misc queries) 1 August 28th 06 09:31 AM
'of' percentage with calculation Steve Crowther Excel Discussion (Misc queries) 2 May 22nd 06 09:58 AM
reverse principle calculation Demi Excel Worksheet Functions 3 March 30th 06 12:40 AM
Percentage calculation Roz Excel Discussion (Misc queries) 5 January 6th 06 08:05 PM


All times are GMT +1. The time now is 09:44 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.