Percentage Calculation

If someone could offer a solution on a percentage formula based on the table below I would greatly appreciate it:

if the value in cell G6 is $00.01 - $25.00, the percentage is 5.25%
if the value in cell G6 is $25.01 - $1000.00, the percentage is 2.75%
if the value in cell G6 is $1000.01 and greater, the percentage is 1.50%

For example, if I have a value of $1,255.00 in cell G6, I need to calculate the first $25.00 at 5.25%, $975 at 2.75% and the remainder of $255.00 at 1.50% added together and output a value in G7.

Doing this manually, i end up with $31.95 in cell G7 ($1.3125 + $26.8125 + $3.825)

Help

Several options.

One from Chip Pearson:

http://www.cpearson.com/excel/pricing.htm

Another one is to use the User Defined Function below
Go to the VB Editor (ALT+F11)
Insert>Module
Paste the funcion in the code window
Use the function from your worksheet
' ===============================
Function PercPerSegment(Amount As Double, Table As Range) As Double
' Progressive pricing
' First argument is the quantity to be priced
' or the amount to be taxed
' Second argument is the Price or Tax% table (vertical)
' Make sure both ends of the table are correct;
' usually you start with zero and the corresponding price or %
' Any value should be found within the limits of the table, so
' if the top slice is infinite, then use
' something like 99999999999999999 as threshold
' and =NA() as corresponding value

Dim StillLeft As Double
Dim AmountThisSlice As Double
Dim SumSoFar As Double
Dim Counter As Long

StillLeft = Amount

For Counter = 1 To Table.Rows.Count - 1
AmountThisSlice = Application.Min(StillLeft, Table(Counter + 1, 1) _
- Table(Counter, 1))
SumSoFar = SumSoFar + AmountThisSlice * Table(Counter, 2)
StillLeft = StillLeft - AmountThisSlice
Next
PercPerSegment = SumSoFar
End Function
' ===============================

--
Kind regards,

Niek Otten

Microsoft MVP - Excel


"clandis" <[email protected]> wrote in
message news:[email protected]...
>
> If someone could offer a solution on a percentage formula based on the
> table below I would greatly appreciate it:
>
> if the value in cell G6 is $00.01 - $25.00, the percentage is 5.25%
> if the value in cell G6 is $25.01 - $1000.00, the percentage is 2.75%
> if the value in cell G6 is $1000.01 and greater, the percentage is
> 1.50%
>
> For example, if I have a value of $1,255.00 in cell G6, I need to
> calculate the first $25.00 at 5.25%, $975 at 2.75% and the remainder of
> $255.00 at 1.50% added together and output a value in G7.
>
> Doing this manually, i end up with $31.95 in cell G7 ($1.3125 +
> $26.8125 + $3.825)
>
> Help
>
>
> --
> clandis
> ------------------------------------------------------------------------
> clandis's Profile:
> http://www.excelforum.com/member.php...o&userid=25468
> View this thread: http://www.excelforum.com/showthread...hreadid=389079
>

=MIN(25,G6)*5.25%+(MIN(975,G6-25)*2.75%)*(G6>25)+MAX(0,G6-1000)*1.5%


"clandis" <[email protected]> wrote in
message news:[email protected]...
>
> If someone could offer a solution on a percentage formula based on the
> table below I would greatly appreciate it:
>
> if the value in cell G6 is $00.01 - $25.00, the percentage is 5.25%
> if the value in cell G6 is $25.01 - $1000.00, the percentage is 2.75%
> if the value in cell G6 is $1000.01 and greater, the percentage is
> 1.50%
>
> For example, if I have a value of $1,255.00 in cell G6, I need to
> calculate the first $25.00 at 5.25%, $975 at 2.75% and the remainder of
> $255.00 at 1.50% added together and output a value in G7.
>
> Doing this manually, i end up with $31.95 in cell G7 ($1.3125 +
> $26.8125 + $3.825)
>
> Help
>
>
> --
> clandis
> ------------------------------------------------------------------------
> clandis's Profile:
> http://www.excelforum.com/member.php...o&userid=25468
> View this thread: http://www.excelforum.com/showthread...hreadid=389079
>

Thanks for the help. It worked perfectly.

One way, for all your eBay needs:

=ROUND(SUMPRODUCT(--(G6>{0,25,1000}),(G6-{0,25,1000}),
{0.0525,-0.025,-0.0125}),2)

You can find other variations, including using a table of rates at

http://www.mcgimpsey.com/excel/variablerates.html



In article <[email protected]>,
clandis <[email protected]> wrote:

> If someone could offer a solution on a percentage formula based on the
> table below I would greatly appreciate it:
>
> if the value in cell G6 is $00.01 - $25.00, the percentage is 5.25%
> if the value in cell G6 is $25.01 - $1000.00, the percentage is 2.75%
> if the value in cell G6 is $1000.01 and greater, the percentage is
> 1.50%
>
> For example, if I have a value of $1,255.00 in cell G6, I need to
> calculate the first $25.00 at 5.25%, $975 at 2.75% and the remainder of
> $255.00 at 1.50% added together and output a value in G7.
>
> Doing this manually, i end up with $31.95 in cell G7 ($1.3125 +
> $26.8125 + $3.825)

Maybe a series of IF instructions would work. I can't write the formula for
you, but you would need three strings:

If value in G6 is >.01<25, the output is G6 times .0525
If value is G6 is >25<1000, output is 1.3125 plus (G6-25) times .0275
If value in G6 is >1000, output is 1.3125 plus 26.8125 plus (G6-1000) times
..015

"clandis" <[email protected]> wrote in
message news:[email protected]...
>
> If someone could offer a solution on a percentage formula based on the
> table below I would greatly appreciate it:
>
> if the value in cell G6 is $00.01 - $25.00, the percentage is 5.25%
> if the value in cell G6 is $25.01 - $1000.00, the percentage is 2.75%
> if the value in cell G6 is $1000.01 and greater, the percentage is
> 1.50%
>
> For example, if I have a value of $1,255.00 in cell G6, I need to
> calculate the first $25.00 at 5.25%, $975 at 2.75% and the remainder of
> $255.00 at 1.50% added together and output a value in G7.
>
> Doing this manually, i end up with $31.95 in cell G7 ($1.3125 +
> $26.8125 + $3.825)
>
> Help
>
>
> --
> clandis
> ------------------------------------------------------------------------
> clandis's Profile:
> http://www.excelforum.com/member.php...o&userid=25468
> View this thread: http://www.excelforum.com/showthread...hreadid=389079
>

Maybe a series of IF instructions would work. I can't write the formula for
you, but you would need three strings:

If value in G6 is >.01<25, the output is G6 times .0525
If value is G6 is >25<1000, output is 1.3125 plus (G6-25) times .0275
If value in G6 is >1000, output is 1.3125 plus 26.8125 plus (G6-1000) times
..015

"clandis" <[email protected]> wrote in
message news:[email protected]...
>
> If someone could offer a solution on a percentage formula based on the
> table below I would greatly appreciate it:
>
> if the value in cell G6 is $00.01 - $25.00, the percentage is 5.25%
> if the value in cell G6 is $25.01 - $1000.00, the percentage is 2.75%
> if the value in cell G6 is $1000.01 and greater, the percentage is
> 1.50%
>
> For example, if I have a value of $1,255.00 in cell G6, I need to
> calculate the first $25.00 at 5.25%, $975 at 2.75% and the remainder of
> $255.00 at 1.50% added together and output a value in G7.
>
> Doing this manually, i end up with $31.95 in cell G7 ($1.3125 +
> $26.8125 + $3.825)
>
> Help
>
>
> --
> clandis
> ------------------------------------------------------------------------
> clandis's Profile:
> http://www.excelforum.com/member.php...o&userid=25468
> View this thread: http://www.excelforum.com/showthread...hreadid=389079
>

One way, for all your eBay needs:

=ROUND(SUMPRODUCT(--(G6>{0,25,1000}),(G6-{0,25,1000}),
{0.0525,-0.025,-0.0125}),2)

You can find other variations, including using a table of rates at

http://www.mcgimpsey.com/excel/variablerates.html



In article <[email protected]>,
clandis <[email protected]> wrote:

> If someone could offer a solution on a percentage formula based on the
> table below I would greatly appreciate it:
>
> if the value in cell G6 is $00.01 - $25.00, the percentage is 5.25%
> if the value in cell G6 is $25.01 - $1000.00, the percentage is 2.75%
> if the value in cell G6 is $1000.01 and greater, the percentage is
> 1.50%
>
> For example, if I have a value of $1,255.00 in cell G6, I need to
> calculate the first $25.00 at 5.25%, $975 at 2.75% and the remainder of
> $255.00 at 1.50% added together and output a value in G7.
>
> Doing this manually, i end up with $31.95 in cell G7 ($1.3125 +
> $26.8125 + $3.825)

=MIN(25,G6)*5.25%+(MIN(975,G6-25)*2.75%)*(G6>25)+MAX(0,G6-1000)*1.5%


"clandis" <[email protected]> wrote in
message news:[email protected]...
>
> If someone could offer a solution on a percentage formula based on the
> table below I would greatly appreciate it:
>
> if the value in cell G6 is $00.01 - $25.00, the percentage is 5.25%
> if the value in cell G6 is $25.01 - $1000.00, the percentage is 2.75%
> if the value in cell G6 is $1000.01 and greater, the percentage is
> 1.50%
>
> For example, if I have a value of $1,255.00 in cell G6, I need to
> calculate the first $25.00 at 5.25%, $975 at 2.75% and the remainder of
> $255.00 at 1.50% added together and output a value in G7.
>
> Doing this manually, i end up with $31.95 in cell G7 ($1.3125 +
> $26.8125 + $3.825)
>
> Help
>
>
> --
> clandis
> ------------------------------------------------------------------------
> clandis's Profile:
> http://www.excelforum.com/member.php...o&userid=25468
> View this thread: http://www.excelforum.com/showthread...hreadid=389079
>

Several options.

One from Chip Pearson:

http://www.cpearson.com/excel/pricing.htm

Another one is to use the User Defined Function below
Go to the VB Editor (ALT+F11)
Insert>Module
Paste the funcion in the code window
Use the function from your worksheet
' ===============================
Function PercPerSegment(Amount As Double, Table As Range) As Double
' Progressive pricing
' First argument is the quantity to be priced
' or the amount to be taxed
' Second argument is the Price or Tax% table (vertical)
' Make sure both ends of the table are correct;
' usually you start with zero and the corresponding price or %
' Any value should be found within the limits of the table, so
' if the top slice is infinite, then use
' something like 99999999999999999 as threshold
' and =NA() as corresponding value

Dim StillLeft As Double
Dim AmountThisSlice As Double
Dim SumSoFar As Double
Dim Counter As Long

StillLeft = Amount

For Counter = 1 To Table.Rows.Count - 1
AmountThisSlice = Application.Min(StillLeft, Table(Counter + 1, 1) _
- Table(Counter, 1))
SumSoFar = SumSoFar + AmountThisSlice * Table(Counter, 2)
StillLeft = StillLeft - AmountThisSlice
Next
PercPerSegment = SumSoFar
End Function
' ===============================

--
Kind regards,

Niek Otten

Microsoft MVP - Excel


"clandis" <[email protected]> wrote in
message news:[email protected]...
>
> If someone could offer a solution on a percentage formula based on the
> table below I would greatly appreciate it:
>
> if the value in cell G6 is $00.01 - $25.00, the percentage is 5.25%
> if the value in cell G6 is $25.01 - $1000.00, the percentage is 2.75%
> if the value in cell G6 is $1000.01 and greater, the percentage is
> 1.50%
>
> For example, if I have a value of $1,255.00 in cell G6, I need to
> calculate the first $25.00 at 5.25%, $975 at 2.75% and the remainder of
> $255.00 at 1.50% added together and output a value in G7.
>
> Doing this manually, i end up with $31.95 in cell G7 ($1.3125 +
> $26.8125 + $3.825)
>
> Help
>
>
> --
> clandis
> ------------------------------------------------------------------------
> clandis's Profile:
> http://www.excelforum.com/member.php...o&userid=25468
> View this thread: http://www.excelforum.com/showthread...hreadid=389079
>

Several options.

One from Chip Pearson:

http://www.cpearson.com/excel/pricing.htm

Another one is to use the User Defined Function below
Go to the VB Editor (ALT+F11)
Insert>Module
Paste the funcion in the code window
Use the function from your worksheet
' ===============================
Function PercPerSegment(Amount As Double, Table As Range) As Double
' Progressive pricing
' First argument is the quantity to be priced
' or the amount to be taxed
' Second argument is the Price or Tax% table (vertical)
' Make sure both ends of the table are correct;
' usually you start with zero and the corresponding price or %
' Any value should be found within the limits of the table, so
' if the top slice is infinite, then use
' something like 99999999999999999 as threshold
' and =NA() as corresponding value

Dim StillLeft As Double
Dim AmountThisSlice As Double
Dim SumSoFar As Double
Dim Counter As Long

StillLeft = Amount

For Counter = 1 To Table.Rows.Count - 1
AmountThisSlice = Application.Min(StillLeft, Table(Counter + 1, 1) _
- Table(Counter, 1))
SumSoFar = SumSoFar + AmountThisSlice * Table(Counter, 2)
StillLeft = StillLeft - AmountThisSlice
Next
PercPerSegment = SumSoFar
End Function
' ===============================

--
Kind regards,

Niek Otten

Microsoft MVP - Excel


"clandis" <[email protected]> wrote in
message news:[email protected]...
>
> If someone could offer a solution on a percentage formula based on the
> table below I would greatly appreciate it:
>
> if the value in cell G6 is $00.01 - $25.00, the percentage is 5.25%
> if the value in cell G6 is $25.01 - $1000.00, the percentage is 2.75%
> if the value in cell G6 is $1000.01 and greater, the percentage is
> 1.50%
>
> For example, if I have a value of $1,255.00 in cell G6, I need to
> calculate the first $25.00 at 5.25%, $975 at 2.75% and the remainder of
> $255.00 at 1.50% added together and output a value in G7.
>
> Doing this manually, i end up with $31.95 in cell G7 ($1.3125 +
> $26.8125 + $3.825)
>
> Help
>
>
> --
> clandis
> ------------------------------------------------------------------------
> clandis's Profile:
> http://www.excelforum.com/member.php...o&userid=25468
> View this thread: http://www.excelforum.com/showthread...hreadid=389079
>

=MIN(25,G6)*5.25%+(MIN(975,G6-25)*2.75%)*(G6>25)+MAX(0,G6-1000)*1.5%


"clandis" <[email protected]> wrote in
message news:[email protected]...
>
> If someone could offer a solution on a percentage formula based on the
> table below I would greatly appreciate it:
>
> if the value in cell G6 is $00.01 - $25.00, the percentage is 5.25%
> if the value in cell G6 is $25.01 - $1000.00, the percentage is 2.75%
> if the value in cell G6 is $1000.01 and greater, the percentage is
> 1.50%
>
> For example, if I have a value of $1,255.00 in cell G6, I need to
> calculate the first $25.00 at 5.25%, $975 at 2.75% and the remainder of
> $255.00 at 1.50% added together and output a value in G7.
>
> Doing this manually, i end up with $31.95 in cell G7 ($1.3125 +
> $26.8125 + $3.825)
>
> Help
>
>
> --
> clandis
> ------------------------------------------------------------------------
> clandis's Profile:
> http://www.excelforum.com/member.php...o&userid=25468
> View this thread: http://www.excelforum.com/showthread...hreadid=389079
>

One way, for all your eBay needs:

=ROUND(SUMPRODUCT(--(G6>{0,25,1000}),(G6-{0,25,1000}),
{0.0525,-0.025,-0.0125}),2)

You can find other variations, including using a table of rates at

http://www.mcgimpsey.com/excel/variablerates.html



In article <[email protected]>,
clandis <[email protected]> wrote:

> If someone could offer a solution on a percentage formula based on the
> table below I would greatly appreciate it:
>
> if the value in cell G6 is $00.01 - $25.00, the percentage is 5.25%
> if the value in cell G6 is $25.01 - $1000.00, the percentage is 2.75%
> if the value in cell G6 is $1000.01 and greater, the percentage is
> 1.50%
>
> For example, if I have a value of $1,255.00 in cell G6, I need to
> calculate the first $25.00 at 5.25%, $975 at 2.75% and the remainder of
> $255.00 at 1.50% added together and output a value in G7.
>
> Doing this manually, i end up with $31.95 in cell G7 ($1.3125 +
> $26.8125 + $3.825)

Maybe a series of IF instructions would work. I can't write the formula for
you, but you would need three strings:

If value in G6 is >.01<25, the output is G6 times .0525
If value is G6 is >25<1000, output is 1.3125 plus (G6-25) times .0275
If value in G6 is >1000, output is 1.3125 plus 26.8125 plus (G6-1000) times
..015

"clandis" <[email protected]> wrote in
message news:[email protected]...
>
> If someone could offer a solution on a percentage formula based on the
> table below I would greatly appreciate it:
>
> if the value in cell G6 is $00.01 - $25.00, the percentage is 5.25%
> if the value in cell G6 is $25.01 - $1000.00, the percentage is 2.75%
> if the value in cell G6 is $1000.01 and greater, the percentage is
> 1.50%
>
> For example, if I have a value of $1,255.00 in cell G6, I need to
> calculate the first $25.00 at 5.25%, $975 at 2.75% and the remainder of
> $255.00 at 1.50% added together and output a value in G7.
>
> Doing this manually, i end up with $31.95 in cell G7 ($1.3125 +
> $26.8125 + $3.825)
>
> Help
>
>
> --
> clandis
> ------------------------------------------------------------------------
> clandis's Profile:
> http://www.excelforum.com/member.php...o&userid=25468
> View this thread: http://www.excelforum.com/showthread...hreadid=389079
>

Maybe a series of IF instructions would work. I can't write the formula for
you, but you would need three strings:

If value in G6 is >.01<25, the output is G6 times .0525
If value is G6 is >25<1000, output is 1.3125 plus (G6-25) times .0275
If value in G6 is >1000, output is 1.3125 plus 26.8125 plus (G6-1000) times
..015

"clandis" <[email protected]> wrote in
message news:[email protected]...
>
> If someone could offer a solution on a percentage formula based on the
> table below I would greatly appreciate it:
>
> if the value in cell G6 is $00.01 - $25.00, the percentage is 5.25%
> if the value in cell G6 is $25.01 - $1000.00, the percentage is 2.75%
> if the value in cell G6 is $1000.01 and greater, the percentage is
> 1.50%
>
> For example, if I have a value of $1,255.00 in cell G6, I need to
> calculate the first $25.00 at 5.25%, $975 at 2.75% and the remainder of
> $255.00 at 1.50% added together and output a value in G7.
>
> Doing this manually, i end up with $31.95 in cell G7 ($1.3125 +
> $26.8125 + $3.825)
>
> Help
>
>
> --
> clandis
> ------------------------------------------------------------------------
> clandis's Profile:
> http://www.excelforum.com/member.php...o&userid=25468
> View this thread: http://www.excelforum.com/showthread...hreadid=389079
>

One way, for all your eBay needs:

=ROUND(SUMPRODUCT(--(G6>{0,25,1000}),(G6-{0,25,1000}),
{0.0525,-0.025,-0.0125}),2)

You can find other variations, including using a table of rates at

http://www.mcgimpsey.com/excel/variablerates.html



In article <[email protected]>,
clandis <[email protected]> wrote:

> If someone could offer a solution on a percentage formula based on the
> table below I would greatly appreciate it:
>
> if the value in cell G6 is $00.01 - $25.00, the percentage is 5.25%
> if the value in cell G6 is $25.01 - $1000.00, the percentage is 2.75%
> if the value in cell G6 is $1000.01 and greater, the percentage is
> 1.50%
>
> For example, if I have a value of $1,255.00 in cell G6, I need to
> calculate the first $25.00 at 5.25%, $975 at 2.75% and the remainder of
> $255.00 at 1.50% added together and output a value in G7.
>
> Doing this manually, i end up with $31.95 in cell G7 ($1.3125 +
> $26.8125 + $3.825)

=MIN(25,G6)*5.25%+(MIN(975,G6-25)*2.75%)*(G6>25)+MAX(0,G6-1000)*1.5%


"clandis" <[email protected]> wrote in
message news:[email protected]...
>
> If someone could offer a solution on a percentage formula based on the
> table below I would greatly appreciate it:
>
> if the value in cell G6 is $00.01 - $25.00, the percentage is 5.25%
> if the value in cell G6 is $25.01 - $1000.00, the percentage is 2.75%
> if the value in cell G6 is $1000.01 and greater, the percentage is
> 1.50%
>
> For example, if I have a value of $1,255.00 in cell G6, I need to
> calculate the first $25.00 at 5.25%, $975 at 2.75% and the remainder of
> $255.00 at 1.50% added together and output a value in G7.
>
> Doing this manually, i end up with $31.95 in cell G7 ($1.3125 +
> $26.8125 + $3.825)
>
> Help
>
>
> --
> clandis
> ------------------------------------------------------------------------
> clandis's Profile:
> http://www.excelforum.com/member.php...o&userid=25468
> View this thread: http://www.excelforum.com/showthread...hreadid=389079
>

Several options.

One from Chip Pearson:

http://www.cpearson.com/excel/pricing.htm

Another one is to use the User Defined Function below
Go to the VB Editor (ALT+F11)
Insert>Module
Paste the funcion in the code window
Use the function from your worksheet
' ===============================
Function PercPerSegment(Amount As Double, Table As Range) As Double
' Progressive pricing
' First argument is the quantity to be priced
' or the amount to be taxed
' Second argument is the Price or Tax% table (vertical)
' Make sure both ends of the table are correct;
' usually you start with zero and the corresponding price or %
' Any value should be found within the limits of the table, so
' if the top slice is infinite, then use
' something like 99999999999999999 as threshold
' and =NA() as corresponding value

Dim StillLeft As Double
Dim AmountThisSlice As Double
Dim SumSoFar As Double
Dim Counter As Long

StillLeft = Amount

For Counter = 1 To Table.Rows.Count - 1
AmountThisSlice = Application.Min(StillLeft, Table(Counter + 1, 1) _
- Table(Counter, 1))
SumSoFar = SumSoFar + AmountThisSlice * Table(Counter, 2)
StillLeft = StillLeft - AmountThisSlice
Next
PercPerSegment = SumSoFar
End Function
' ===============================

--
Kind regards,

Niek Otten

Microsoft MVP - Excel


"clandis" <[email protected]> wrote in
message news:[email protected]...
>
> If someone could offer a solution on a percentage formula based on the
> table below I would greatly appreciate it:
>
> if the value in cell G6 is $00.01 - $25.00, the percentage is 5.25%
> if the value in cell G6 is $25.01 - $1000.00, the percentage is 2.75%
> if the value in cell G6 is $1000.01 and greater, the percentage is
> 1.50%
>
> For example, if I have a value of $1,255.00 in cell G6, I need to
> calculate the first $25.00 at 5.25%, $975 at 2.75% and the remainder of
> $255.00 at 1.50% added together and output a value in G7.
>
> Doing this manually, i end up with $31.95 in cell G7 ($1.3125 +
> $26.8125 + $3.825)
>
> Help
>
>
> --
> clandis
> ------------------------------------------------------------------------
> clandis's Profile:
> http://www.excelforum.com/member.php...o&userid=25468
> View this thread: http://www.excelforum.com/showthread...hreadid=389079
>

One way, for all your eBay needs:

=ROUND(SUMPRODUCT(--(G6>{0,25,1000}),(G6-{0,25,1000}),
{0.0525,-0.025,-0.0125}),2)

You can find other variations, including using a table of rates at

http://www.mcgimpsey.com/excel/variablerates.html



In article <[email protected]>,
clandis <[email protected]> wrote:

> If someone could offer a solution on a percentage formula based on the
> table below I would greatly appreciate it:
>
> if the value in cell G6 is $00.01 - $25.00, the percentage is 5.25%
> if the value in cell G6 is $25.01 - $1000.00, the percentage is 2.75%
> if the value in cell G6 is $1000.01 and greater, the percentage is
> 1.50%
>
> For example, if I have a value of $1,255.00 in cell G6, I need to
> calculate the first $25.00 at 5.25%, $975 at 2.75% and the remainder of
> $255.00 at 1.50% added together and output a value in G7.
>
> Doing this manually, i end up with $31.95 in cell G7 ($1.3125 +
> $26.8125 + $3.825)

Maybe a series of IF instructions would work. I can't write the formula for
you, but you would need three strings:

If value in G6 is >.01<25, the output is G6 times .0525
If value is G6 is >25<1000, output is 1.3125 plus (G6-25) times .0275
If value in G6 is >1000, output is 1.3125 plus 26.8125 plus (G6-1000) times
..015

"clandis" <[email protected]> wrote in
message news:[email protected]...
>
> If someone could offer a solution on a percentage formula based on the
> table below I would greatly appreciate it:
>
> if the value in cell G6 is $00.01 - $25.00, the percentage is 5.25%
> if the value in cell G6 is $25.01 - $1000.00, the percentage is 2.75%
> if the value in cell G6 is $1000.01 and greater, the percentage is
> 1.50%
>
> For example, if I have a value of $1,255.00 in cell G6, I need to
> calculate the first $25.00 at 5.25%, $975 at 2.75% and the remainder of
> $255.00 at 1.50% added together and output a value in G7.
>
> Doing this manually, i end up with $31.95 in cell G7 ($1.3125 +
> $26.8125 + $3.825)
>
> Help
>
>
> --
> clandis
> ------------------------------------------------------------------------
> clandis's Profile:
> http://www.excelforum.com/member.php...o&userid=25468
> View this thread: http://www.excelforum.com/showthread...hreadid=389079
>

=MIN(25,G6)*5.25%+(MIN(975,G6-25)*2.75%)*(G6>25)+MAX(0,G6-1000)*1.5%


"clandis" <[email protected]> wrote in
message news:[email protected]...
>
> If someone could offer a solution on a percentage formula based on the
> table below I would greatly appreciate it:
>
> if the value in cell G6 is $00.01 - $25.00, the percentage is 5.25%
> if the value in cell G6 is $25.01 - $1000.00, the percentage is 2.75%
> if the value in cell G6 is $1000.01 and greater, the percentage is
> 1.50%
>
> For example, if I have a value of $1,255.00 in cell G6, I need to
> calculate the first $25.00 at 5.25%, $975 at 2.75% and the remainder of
> $255.00 at 1.50% added together and output a value in G7.
>
> Doing this manually, i end up with $31.95 in cell G7 ($1.3125 +
> $26.8125 + $3.825)
>
> Help
>
>
> --
> clandis
> ------------------------------------------------------------------------
> clandis's Profile:
> http://www.excelforum.com/member.php...o&userid=25468
> View this thread: http://www.excelforum.com/showthread...hreadid=389079
>

Several options.

One from Chip Pearson:

http://www.cpearson.com/excel/pricing.htm

Another one is to use the User Defined Function below
Go to the VB Editor (ALT+F11)
Insert>Module
Paste the funcion in the code window
Use the function from your worksheet
' ===============================
Function PercPerSegment(Amount As Double, Table As Range) As Double
' Progressive pricing
' First argument is the quantity to be priced
' or the amount to be taxed
' Second argument is the Price or Tax% table (vertical)
' Make sure both ends of the table are correct;
' usually you start with zero and the corresponding price or %
' Any value should be found within the limits of the table, so
' if the top slice is infinite, then use
' something like 99999999999999999 as threshold
' and =NA() as corresponding value

Dim StillLeft As Double
Dim AmountThisSlice As Double
Dim SumSoFar As Double
Dim Counter As Long

StillLeft = Amount

For Counter = 1 To Table.Rows.Count - 1
AmountThisSlice = Application.Min(StillLeft, Table(Counter + 1, 1) _
- Table(Counter, 1))
SumSoFar = SumSoFar + AmountThisSlice * Table(Counter, 2)
StillLeft = StillLeft - AmountThisSlice
Next
PercPerSegment = SumSoFar
End Function
' ===============================

--
Kind regards,

Niek Otten

Microsoft MVP - Excel


"clandis" <[email protected]> wrote in
message news:[email protected]...
>
> If someone could offer a solution on a percentage formula based on the
> table below I would greatly appreciate it:
>
> if the value in cell G6 is $00.01 - $25.00, the percentage is 5.25%
> if the value in cell G6 is $25.01 - $1000.00, the percentage is 2.75%
> if the value in cell G6 is $1000.01 and greater, the percentage is
> 1.50%
>
> For example, if I have a value of $1,255.00 in cell G6, I need to
> calculate the first $25.00 at 5.25%, $975 at 2.75% and the remainder of
> $255.00 at 1.50% added together and output a value in G7.
>
> Doing this manually, i end up with $31.95 in cell G7 ($1.3125 +
> $26.8125 + $3.825)
>
> Help
>
>
> --
> clandis
> ------------------------------------------------------------------------
> clandis's Profile:
> http://www.excelforum.com/member.php...o&userid=25468
> View this thread: http://www.excelforum.com/showthread...hreadid=389079
>

One way, for all your eBay needs:

=ROUND(SUMPRODUCT(--(G6>{0,25,1000}),(G6-{0,25,1000}),
{0.0525,-0.025,-0.0125}),2)

You can find other variations, including using a table of rates at

http://www.mcgimpsey.com/excel/variablerates.html



In article <[email protected]>,
clandis <[email protected]> wrote:

> If someone could offer a solution on a percentage formula based on the
> table below I would greatly appreciate it:
>
> if the value in cell G6 is $00.01 - $25.00, the percentage is 5.25%
> if the value in cell G6 is $25.01 - $1000.00, the percentage is 2.75%
> if the value in cell G6 is $1000.01 and greater, the percentage is
> 1.50%
>
> For example, if I have a value of $1,255.00 in cell G6, I need to
> calculate the first $25.00 at 5.25%, $975 at 2.75% and the remainder of
> $255.00 at 1.50% added together and output a value in G7.
>
> Doing this manually, i end up with $31.95 in cell G7 ($1.3125 +
> $26.8125 + $3.825)

Several options.

One from Chip Pearson:

http://www.cpearson.com/excel/pricing.htm

Another one is to use the User Defined Function below
Go to the VB Editor (ALT+F11)
Insert>Module
Paste the funcion in the code window
Use the function from your worksheet
' ===============================
Function PercPerSegment(Amount As Double, Table As Range) As Double
' Progressive pricing
' First argument is the quantity to be priced
' or the amount to be taxed
' Second argument is the Price or Tax% table (vertical)
' Make sure both ends of the table are correct;
' usually you start with zero and the corresponding price or %
' Any value should be found within the limits of the table, so
' if the top slice is infinite, then use
' something like 99999999999999999 as threshold
' and =NA() as corresponding value

Dim StillLeft As Double
Dim AmountThisSlice As Double
Dim SumSoFar As Double
Dim Counter As Long

StillLeft = Amount

For Counter = 1 To Table.Rows.Count - 1
AmountThisSlice = Application.Min(StillLeft, Table(Counter + 1, 1) _
- Table(Counter, 1))
SumSoFar = SumSoFar + AmountThisSlice * Table(Counter, 2)
StillLeft = StillLeft - AmountThisSlice
Next
PercPerSegment = SumSoFar
End Function
' ===============================

--
Kind regards,

Niek Otten

Microsoft MVP - Excel


"clandis" <[email protected]> wrote in
message news:[email protected]...
>
> If someone could offer a solution on a percentage formula based on the
> table below I would greatly appreciate it:
>
> if the value in cell G6 is $00.01 - $25.00, the percentage is 5.25%
> if the value in cell G6 is $25.01 - $1000.00, the percentage is 2.75%
> if the value in cell G6 is $1000.01 and greater, the percentage is
> 1.50%
>
> For example, if I have a value of $1,255.00 in cell G6, I need to
> calculate the first $25.00 at 5.25%, $975 at 2.75% and the remainder of
> $255.00 at 1.50% added together and output a value in G7.
>
> Doing this manually, i end up with $31.95 in cell G7 ($1.3125 +
> $26.8125 + $3.825)
>
> Help
>
>
> --
> clandis
> ------------------------------------------------------------------------
> clandis's Profile:
> http://www.excelforum.com/member.php...o&userid=25468
> View this thread: http://www.excelforum.com/showthread...hreadid=389079
>

Maybe a series of IF instructions would work. I can't write the formula for
you, but you would need three strings:

If value in G6 is >.01<25, the output is G6 times .0525
If value is G6 is >25<1000, output is 1.3125 plus (G6-25) times .0275
If value in G6 is >1000, output is 1.3125 plus 26.8125 plus (G6-1000) times
..015

"clandis" <[email protected]> wrote in
message news:[email protected]...
>
> If someone could offer a solution on a percentage formula based on the
> table below I would greatly appreciate it:
>
> if the value in cell G6 is $00.01 - $25.00, the percentage is 5.25%
> if the value in cell G6 is $25.01 - $1000.00, the percentage is 2.75%
> if the value in cell G6 is $1000.01 and greater, the percentage is
> 1.50%
>
> For example, if I have a value of $1,255.00 in cell G6, I need to
> calculate the first $25.00 at 5.25%, $975 at 2.75% and the remainder of
> $255.00 at 1.50% added together and output a value in G7.
>
> Doing this manually, i end up with $31.95 in cell G7 ($1.3125 +
> $26.8125 + $3.825)
>
> Help
>
>
> --
> clandis
> ------------------------------------------------------------------------
> clandis's Profile:
> http://www.excelforum.com/member.php...o&userid=25468
> View this thread: http://www.excelforum.com/showthread...hreadid=389079
>

=MIN(25,G6)*5.25%+(MIN(975,G6-25)*2.75%)*(G6>25)+MAX(0,G6-1000)*1.5%


"clandis" <[email protected]> wrote in
message news:[email protected]...
>
> If someone could offer a solution on a percentage formula based on the
> table below I would greatly appreciate it:
>
> if the value in cell G6 is $00.01 - $25.00, the percentage is 5.25%
> if the value in cell G6 is $25.01 - $1000.00, the percentage is 2.75%
> if the value in cell G6 is $1000.01 and greater, the percentage is
> 1.50%
>
> For example, if I have a value of $1,255.00 in cell G6, I need to
> calculate the first $25.00 at 5.25%, $975 at 2.75% and the remainder of
> $255.00 at 1.50% added together and output a value in G7.
>
> Doing this manually, i end up with $31.95 in cell G7 ($1.3125 +
> $26.8125 + $3.825)
>
> Help
>
>
> --
> clandis
> ------------------------------------------------------------------------
> clandis's Profile:
> http://www.excelforum.com/member.php...o&userid=25468
> View this thread: http://www.excelforum.com/showthread...hreadid=389079
>

Maybe a series of IF instructions would work. I can't write the formula for
you, but you would need three strings:

If value in G6 is >.01<25, the output is G6 times .0525
If value is G6 is >25<1000, output is 1.3125 plus (G6-25) times .0275
If value in G6 is >1000, output is 1.3125 plus 26.8125 plus (G6-1000) times
..015

"clandis" <[email protected]> wrote in
message news:[email protected]...
>
> If someone could offer a solution on a percentage formula based on the
> table below I would greatly appreciate it:
>
> if the value in cell G6 is $00.01 - $25.00, the percentage is 5.25%
> if the value in cell G6 is $25.01 - $1000.00, the percentage is 2.75%
> if the value in cell G6 is $1000.01 and greater, the percentage is
> 1.50%
>
> For example, if I have a value of $1,255.00 in cell G6, I need to
> calculate the first $25.00 at 5.25%, $975 at 2.75% and the remainder of
> $255.00 at 1.50% added together and output a value in G7.
>
> Doing this manually, i end up with $31.95 in cell G7 ($1.3125 +
> $26.8125 + $3.825)
>
> Help
>
>
> --
> clandis
> ------------------------------------------------------------------------
> clandis's Profile:
> http://www.excelforum.com/member.php...o&userid=25468
> View this thread: http://www.excelforum.com/showthread...hreadid=389079
>

One way, for all your eBay needs:

=ROUND(SUMPRODUCT(--(G6>{0,25,1000}),(G6-{0,25,1000}),
{0.0525,-0.025,-0.0125}),2)

You can find other variations, including using a table of rates at

http://www.mcgimpsey.com/excel/variablerates.html



In article <[email protected]>,
clandis <[email protected]> wrote:

> If someone could offer a solution on a percentage formula based on the
> table below I would greatly appreciate it:
>
> if the value in cell G6 is $00.01 - $25.00, the percentage is 5.25%
> if the value in cell G6 is $25.01 - $1000.00, the percentage is 2.75%
> if the value in cell G6 is $1000.01 and greater, the percentage is
> 1.50%
>
> For example, if I have a value of $1,255.00 in cell G6, I need to
> calculate the first $25.00 at 5.25%, $975 at 2.75% and the remainder of
> $255.00 at 1.50% added together and output a value in G7.
>
> Doing this manually, i end up with $31.95 in cell G7 ($1.3125 +
> $26.8125 + $3.825)

=MIN(25,G6)*5.25%+(MIN(975,G6-25)*2.75%)*(G6>25)+MAX(0,G6-1000)*1.5%


"clandis" <[email protected]> wrote in
message news:[email protected]...
>
> If someone could offer a solution on a percentage formula based on the
> table below I would greatly appreciate it:
>
> if the value in cell G6 is $00.01 - $25.00, the percentage is 5.25%
> if the value in cell G6 is $25.01 - $1000.00, the percentage is 2.75%
> if the value in cell G6 is $1000.01 and greater, the percentage is
> 1.50%
>
> For example, if I have a value of $1,255.00 in cell G6, I need to
> calculate the first $25.00 at 5.25%, $975 at 2.75% and the remainder of
> $255.00 at 1.50% added together and output a value in G7.
>
> Doing this manually, i end up with $31.95 in cell G7 ($1.3125 +
> $26.8125 + $3.825)
>
> Help
>
>
> --
> clandis
> ------------------------------------------------------------------------
> clandis's Profile:
> http://www.excelforum.com/member.php...o&userid=25468
> View this thread: http://www.excelforum.com/showthread...hreadid=389079
>

Several options.

One from Chip Pearson:

http://www.cpearson.com/excel/pricing.htm

Another one is to use the User Defined Function below
Go to the VB Editor (ALT+F11)
Insert>Module
Paste the funcion in the code window
Use the function from your worksheet
' ===============================
Function PercPerSegment(Amount As Double, Table As Range) As Double
' Progressive pricing
' First argument is the quantity to be priced
' or the amount to be taxed
' Second argument is the Price or Tax% table (vertical)
' Make sure both ends of the table are correct;
' usually you start with zero and the corresponding price or %
' Any value should be found within the limits of the table, so
' if the top slice is infinite, then use
' something like 99999999999999999 as threshold
' and =NA() as corresponding value

Dim StillLeft As Double
Dim AmountThisSlice As Double
Dim SumSoFar As Double
Dim Counter As Long

StillLeft = Amount

For Counter = 1 To Table.Rows.Count - 1
AmountThisSlice = Application.Min(StillLeft, Table(Counter + 1, 1) _
- Table(Counter, 1))
SumSoFar = SumSoFar + AmountThisSlice * Table(Counter, 2)
StillLeft = StillLeft - AmountThisSlice
Next
PercPerSegment = SumSoFar
End Function
' ===============================

--
Kind regards,

Niek Otten

Microsoft MVP - Excel


"clandis" <[email protected]> wrote in
message news:[email protected]...
>
> If someone could offer a solution on a percentage formula based on the
> table below I would greatly appreciate it:
>
> if the value in cell G6 is $00.01 - $25.00, the percentage is 5.25%
> if the value in cell G6 is $25.01 - $1000.00, the percentage is 2.75%
> if the value in cell G6 is $1000.01 and greater, the percentage is
> 1.50%
>
> For example, if I have a value of $1,255.00 in cell G6, I need to
> calculate the first $25.00 at 5.25%, $975 at 2.75% and the remainder of
> $255.00 at 1.50% added together and output a value in G7.
>
> Doing this manually, i end up with $31.95 in cell G7 ($1.3125 +
> $26.8125 + $3.825)
>
> Help
>
>
> --
> clandis
> ------------------------------------------------------------------------
> clandis's Profile:
> http://www.excelforum.com/member.php...o&userid=25468
> View this thread: http://www.excelforum.com/showthread...hreadid=389079
>

Maybe a series of IF instructions would work. I can't write the formula for
you, but you would need three strings:

If value in G6 is >.01<25, the output is G6 times .0525
If value is G6 is >25<1000, output is 1.3125 plus (G6-25) times .0275
If value in G6 is >1000, output is 1.3125 plus 26.8125 plus (G6-1000) times
..015

"clandis" <[email protected]> wrote in
message news:[email protected]...
>
> If someone could offer a solution on a percentage formula based on the
> table below I would greatly appreciate it:
>
> if the value in cell G6 is $00.01 - $25.00, the percentage is 5.25%
> if the value in cell G6 is $25.01 - $1000.00, the percentage is 2.75%
> if the value in cell G6 is $1000.01 and greater, the percentage is
> 1.50%
>
> For example, if I have a value of $1,255.00 in cell G6, I need to
> calculate the first $25.00 at 5.25%, $975 at 2.75% and the remainder of
> $255.00 at 1.50% added together and output a value in G7.
>
> Doing this manually, i end up with $31.95 in cell G7 ($1.3125 +
> $26.8125 + $3.825)
>
> Help
>
>
> --
> clandis
> ------------------------------------------------------------------------
> clandis's Profile:
> http://www.excelforum.com/member.php...o&userid=25468
> View this thread: http://www.excelforum.com/showthread...hreadid=389079
>

One way, for all your eBay needs:

=ROUND(SUMPRODUCT(--(G6>{0,25,1000}),(G6-{0,25,1000}),
{0.0525,-0.025,-0.0125}),2)

You can find other variations, including using a table of rates at

http://www.mcgimpsey.com/excel/variablerates.html



In article <[email protected]>,
clandis <[email protected]> wrote:

> If someone could offer a solution on a percentage formula based on the
> table below I would greatly appreciate it:
>
> if the value in cell G6 is $00.01 - $25.00, the percentage is 5.25%
> if the value in cell G6 is $25.01 - $1000.00, the percentage is 2.75%
> if the value in cell G6 is $1000.01 and greater, the percentage is
> 1.50%
>
> For example, if I have a value of $1,255.00 in cell G6, I need to
> calculate the first $25.00 at 5.25%, $975 at 2.75% and the remainder of
> $255.00 at 1.50% added together and output a value in G7.
>
> Doing this manually, i end up with $31.95 in cell G7 ($1.3125 +
> $26.8125 + $3.825)

=MIN(25,G6)*5.25%+(MIN(975,G6-25)*2.75%)*(G6>25)+MAX(0,G6-1000)*1.5%


"clandis" <[email protected]> wrote in
message news:[email protected]...
>
> If someone could offer a solution on a percentage formula based on the
> table below I would greatly appreciate it:
>
> if the value in cell G6 is $00.01 - $25.00, the percentage is 5.25%
> if the value in cell G6 is $25.01 - $1000.00, the percentage is 2.75%
> if the value in cell G6 is $1000.01 and greater, the percentage is
> 1.50%
>
> For example, if I have a value of $1,255.00 in cell G6, I need to
> calculate the first $25.00 at 5.25%, $975 at 2.75% and the remainder of
> $255.00 at 1.50% added together and output a value in G7.
>
> Doing this manually, i end up with $31.95 in cell G7 ($1.3125 +
> $26.8125 + $3.825)
>
> Help
>
>
> --
> clandis
> ------------------------------------------------------------------------
> clandis's Profile:
> http://www.excelforum.com/member.php...o&userid=25468
> View this thread: http://www.excelforum.com/showthread...hreadid=389079
>

Several options.

One from Chip Pearson:

http://www.cpearson.com/excel/pricing.htm

Another one is to use the User Defined Function below
Go to the VB Editor (ALT+F11)
Insert>Module
Paste the funcion in the code window
Use the function from your worksheet
' ===============================
Function PercPerSegment(Amount As Double, Table As Range) As Double
' Progressive pricing
' First argument is the quantity to be priced
' or the amount to be taxed
' Second argument is the Price or Tax% table (vertical)
' Make sure both ends of the table are correct;
' usually you start with zero and the corresponding price or %
' Any value should be found within the limits of the table, so
' if the top slice is infinite, then use
' something like 99999999999999999 as threshold
' and =NA() as corresponding value

Dim StillLeft As Double
Dim AmountThisSlice As Double
Dim SumSoFar As Double
Dim Counter As Long

StillLeft = Amount

For Counter = 1 To Table.Rows.Count - 1
AmountThisSlice = Application.Min(StillLeft, Table(Counter + 1, 1) _
- Table(Counter, 1))
SumSoFar = SumSoFar + AmountThisSlice * Table(Counter, 2)
StillLeft = StillLeft - AmountThisSlice
Next
PercPerSegment = SumSoFar
End Function
' ===============================

--
Kind regards,

Niek Otten

Microsoft MVP - Excel


"clandis" <[email protected]> wrote in
message news:[email protected]...
>
> If someone could offer a solution on a percentage formula based on the
> table below I would greatly appreciate it:
>
> if the value in cell G6 is $00.01 - $25.00, the percentage is 5.25%
> if the value in cell G6 is $25.01 - $1000.00, the percentage is 2.75%
> if the value in cell G6 is $1000.01 and greater, the percentage is
> 1.50%
>
> For example, if I have a value of $1,255.00 in cell G6, I need to
> calculate the first $25.00 at 5.25%, $975 at 2.75% and the remainder of
> $255.00 at 1.50% added together and output a value in G7.
>
> Doing this manually, i end up with $31.95 in cell G7 ($1.3125 +
> $26.8125 + $3.825)
>
> Help
>
>
> --
> clandis
> ------------------------------------------------------------------------
> clandis's Profile:
> http://www.excelforum.com/member.php...o&userid=25468
> View this thread: http://www.excelforum.com/showthread...hreadid=389079
>

Maybe a series of IF instructions would work. I can't write the formula for
you, but you would need three strings:

If value in G6 is >.01<25, the output is G6 times .0525
If value is G6 is >25<1000, output is 1.3125 plus (G6-25) times .0275
If value in G6 is >1000, output is 1.3125 plus 26.8125 plus (G6-1000) times
..015

"clandis" <[email protected]> wrote in
message news:[email protected]...
>
> If someone could offer a solution on a percentage formula based on the
> table below I would greatly appreciate it:
>
> if the value in cell G6 is $00.01 - $25.00, the percentage is 5.25%
> if the value in cell G6 is $25.01 - $1000.00, the percentage is 2.75%
> if the value in cell G6 is $1000.01 and greater, the percentage is
> 1.50%
>
> For example, if I have a value of $1,255.00 in cell G6, I need to
> calculate the first $25.00 at 5.25%, $975 at 2.75% and the remainder of
> $255.00 at 1.50% added together and output a value in G7.
>
> Doing this manually, i end up with $31.95 in cell G7 ($1.3125 +
> $26.8125 + $3.825)
>
> Help
>
>
> --
> clandis
> ------------------------------------------------------------------------
> clandis's Profile:
> http://www.excelforum.com/member.php...o&userid=25468
> View this thread: http://www.excelforum.com/showthread...hreadid=389079
>

One way, for all your eBay needs:

=ROUND(SUMPRODUCT(--(G6>{0,25,1000}),(G6-{0,25,1000}),
{0.0525,-0.025,-0.0125}),2)

You can find other variations, including using a table of rates at

http://www.mcgimpsey.com/excel/variablerates.html



In article <[email protected]>,
clandis <[email protected]> wrote:

> If someone could offer a solution on a percentage formula based on the
> table below I would greatly appreciate it:
>
> if the value in cell G6 is $00.01 - $25.00, the percentage is 5.25%
> if the value in cell G6 is $25.01 - $1000.00, the percentage is 2.75%
> if the value in cell G6 is $1000.01 and greater, the percentage is
> 1.50%
>
> For example, if I have a value of $1,255.00 in cell G6, I need to
> calculate the first $25.00 at 5.25%, $975 at 2.75% and the remainder of
> $255.00 at 1.50% added together and output a value in G7.
>
> Doing this manually, i end up with $31.95 in cell G7 ($1.3125 +
> $26.8125 + $3.825)

=MIN(25,G6)*5.25%+(MIN(975,G6-25)*2.75%)*(G6>25)+MAX(0,G6-1000)*1.5%


"clandis" <[email protected]> wrote in
message news:[email protected]...
>
> If someone could offer a solution on a percentage formula based on the
> table below I would greatly appreciate it:
>
> if the value in cell G6 is $00.01 - $25.00, the percentage is 5.25%
> if the value in cell G6 is $25.01 - $1000.00, the percentage is 2.75%
> if the value in cell G6 is $1000.01 and greater, the percentage is
> 1.50%
>
> For example, if I have a value of $1,255.00 in cell G6, I need to
> calculate the first $25.00 at 5.25%, $975 at 2.75% and the remainder of
> $255.00 at 1.50% added together and output a value in G7.
>
> Doing this manually, i end up with $31.95 in cell G7 ($1.3125 +
> $26.8125 + $3.825)
>
> Help
>
>
> --
> clandis
> ------------------------------------------------------------------------
> clandis's Profile:
> http://www.excelforum.com/member.php...o&userid=25468
> View this thread: http://www.excelforum.com/showthread...hreadid=389079
>

Several options.

One from Chip Pearson:

http://www.cpearson.com/excel/pricing.htm

Another one is to use the User Defined Function below
Go to the VB Editor (ALT+F11)
Insert>Module
Paste the funcion in the code window
Use the function from your worksheet
' ===============================
Function PercPerSegment(Amount As Double, Table As Range) As Double
' Progressive pricing
' First argument is the quantity to be priced
' or the amount to be taxed
' Second argument is the Price or Tax% table (vertical)
' Make sure both ends of the table are correct;
' usually you start with zero and the corresponding price or %
' Any value should be found within the limits of the table, so
' if the top slice is infinite, then use
' something like 99999999999999999 as threshold
' and =NA() as corresponding value

Dim StillLeft As Double
Dim AmountThisSlice As Double
Dim SumSoFar As Double
Dim Counter As Long

StillLeft = Amount

For Counter = 1 To Table.Rows.Count - 1
AmountThisSlice = Application.Min(StillLeft, Table(Counter + 1, 1) _
- Table(Counter, 1))
SumSoFar = SumSoFar + AmountThisSlice * Table(Counter, 2)
StillLeft = StillLeft - AmountThisSlice
Next
PercPerSegment = SumSoFar
End Function
' ===============================

--
Kind regards,

Niek Otten

Microsoft MVP - Excel


"clandis" <[email protected]> wrote in
message news:[email protected]...
>
> If someone could offer a solution on a percentage formula based on the
> table below I would greatly appreciate it:
>
> if the value in cell G6 is $00.01 - $25.00, the percentage is 5.25%
> if the value in cell G6 is $25.01 - $1000.00, the percentage is 2.75%
> if the value in cell G6 is $1000.01 and greater, the percentage is
> 1.50%
>
> For example, if I have a value of $1,255.00 in cell G6, I need to
> calculate the first $25.00 at 5.25%, $975 at 2.75% and the remainder of
> $255.00 at 1.50% added together and output a value in G7.
>
> Doing this manually, i end up with $31.95 in cell G7 ($1.3125 +
> $26.8125 + $3.825)
>
> Help
>
>
> --
> clandis
> ------------------------------------------------------------------------
> clandis's Profile:
> http://www.excelforum.com/member.php...o&userid=25468
> View this thread: http://www.excelforum.com/showthread...hreadid=389079
>

=MIN(25,G6)*5.25%+(MIN(975,G6-25)*2.75%)*(G6>25)+MAX(0,G6-1000)*1.5%


"clandis" <[email protected]> wrote in
message news:[email protected]...
>
> If someone could offer a solution on a percentage formula based on the
> table below I would greatly appreciate it:
>
> if the value in cell G6 is $00.01 - $25.00, the percentage is 5.25%
> if the value in cell G6 is $25.01 - $1000.00, the percentage is 2.75%
> if the value in cell G6 is $1000.01 and greater, the percentage is
> 1.50%
>
> For example, if I have a value of $1,255.00 in cell G6, I need to
> calculate the first $25.00 at 5.25%, $975 at 2.75% and the remainder of
> $255.00 at 1.50% added together and output a value in G7.
>
> Doing this manually, i end up with $31.95 in cell G7 ($1.3125 +
> $26.8125 + $3.825)
>
> Help
>
>
> --
> clandis
> ------------------------------------------------------------------------
> clandis's Profile:
> http://www.excelforum.com/member.php...o&userid=25468
> View this thread: http://www.excelforum.com/showthread...hreadid=389079
>

Several options.

One from Chip Pearson:

http://www.cpearson.com/excel/pricing.htm

Another one is to use the User Defined Function below
Go to the VB Editor (ALT+F11)
Insert>Module
Paste the funcion in the code window
Use the function from your worksheet
' ===============================
Function PercPerSegment(Amount As Double, Table As Range) As Double
' Progressive pricing
' First argument is the quantity to be priced
' or the amount to be taxed
' Second argument is the Price or Tax% table (vertical)
' Make sure both ends of the table are correct;
' usually you start with zero and the corresponding price or %
' Any value should be found within the limits of the table, so
' if the top slice is infinite, then use
' something like 99999999999999999 as threshold
' and =NA() as corresponding value

Dim StillLeft As Double
Dim AmountThisSlice As Double
Dim SumSoFar As Double
Dim Counter As Long

StillLeft = Amount

For Counter = 1 To Table.Rows.Count - 1
AmountThisSlice = Application.Min(StillLeft, Table(Counter + 1, 1) _
- Table(Counter, 1))
SumSoFar = SumSoFar + AmountThisSlice * Table(Counter, 2)
StillLeft = StillLeft - AmountThisSlice
Next
PercPerSegment = SumSoFar
End Function
' ===============================

--
Kind regards,

Niek Otten

Microsoft MVP - Excel


"clandis" <[email protected]> wrote in
message news:[email protected]...
>
> If someone could offer a solution on a percentage formula based on the
> table below I would greatly appreciate it:
>
> if the value in cell G6 is $00.01 - $25.00, the percentage is 5.25%
> if the value in cell G6 is $25.01 - $1000.00, the percentage is 2.75%
> if the value in cell G6 is $1000.01 and greater, the percentage is
> 1.50%
>
> For example, if I have a value of $1,255.00 in cell G6, I need to
> calculate the first $25.00 at 5.25%, $975 at 2.75% and the remainder of
> $255.00 at 1.50% added together and output a value in G7.
>
> Doing this manually, i end up with $31.95 in cell G7 ($1.3125 +
> $26.8125 + $3.825)
>
> Help
>
>
> --
> clandis
> ------------------------------------------------------------------------
> clandis's Profile:
> http://www.excelforum.com/member.php...o&userid=25468
> View this thread: http://www.excelforum.com/showthread...hreadid=389079
>

One way, for all your eBay needs:

=ROUND(SUMPRODUCT(--(G6>{0,25,1000}),(G6-{0,25,1000}),
{0.0525,-0.025,-0.0125}),2)

You can find other variations, including using a table of rates at

http://www.mcgimpsey.com/excel/variablerates.html



In article <[email protected]>,
clandis <[email protected]> wrote:

> If someone could offer a solution on a percentage formula based on the
> table below I would greatly appreciate it:
>
> if the value in cell G6 is $00.01 - $25.00, the percentage is 5.25%
> if the value in cell G6 is $25.01 - $1000.00, the percentage is 2.75%
> if the value in cell G6 is $1000.01 and greater, the percentage is
> 1.50%
>
> For example, if I have a value of $1,255.00 in cell G6, I need to
> calculate the first $25.00 at 5.25%, $975 at 2.75% and the remainder of
> $255.00 at 1.50% added together and output a value in G7.
>
> Doing this manually, i end up with $31.95 in cell G7 ($1.3125 +
> $26.8125 + $3.825)

Maybe a series of IF instructions would work. I can't write the formula for
you, but you would need three strings:

If value in G6 is >.01<25, the output is G6 times .0525
If value is G6 is >25<1000, output is 1.3125 plus (G6-25) times .0275
If value in G6 is >1000, output is 1.3125 plus 26.8125 plus (G6-1000) times
..015

"clandis" <[email protected]> wrote in
message news:[email protected]...
>
> If someone could offer a solution on a percentage formula based on the
> table below I would greatly appreciate it:
>
> if the value in cell G6 is $00.01 - $25.00, the percentage is 5.25%
> if the value in cell G6 is $25.01 - $1000.00, the percentage is 2.75%
> if the value in cell G6 is $1000.01 and greater, the percentage is
> 1.50%
>
> For example, if I have a value of $1,255.00 in cell G6, I need to
> calculate the first $25.00 at 5.25%, $975 at 2.75% and the remainder of
> $255.00 at 1.50% added together and output a value in G7.
>
> Doing this manually, i end up with $31.95 in cell G7 ($1.3125 +
> $26.8125 + $3.825)
>
> Help
>
>
> --
> clandis
> ------------------------------------------------------------------------
> clandis's Profile:
> http://www.excelforum.com/member.php...o&userid=25468
> View this thread: http://www.excelforum.com/showthread...hreadid=389079
>

Maybe a series of IF instructions would work. I can't write the formula for
you, but you would need three strings:

If value in G6 is >.01<25, the output is G6 times .0525
If value is G6 is >25<1000, output is 1.3125 plus (G6-25) times .0275
If value in G6 is >1000, output is 1.3125 plus 26.8125 plus (G6-1000) times
..015

"clandis" <[email protected]> wrote in
message news:[email protected]...
>
> If someone could offer a solution on a percentage formula based on the
> table below I would greatly appreciate it:
>
> if the value in cell G6 is $00.01 - $25.00, the percentage is 5.25%
> if the value in cell G6 is $25.01 - $1000.00, the percentage is 2.75%
> if the value in cell G6 is $1000.01 and greater, the percentage is
> 1.50%
>
> For example, if I have a value of $1,255.00 in cell G6, I need to
> calculate the first $25.00 at 5.25%, $975 at 2.75% and the remainder of
> $255.00 at 1.50% added together and output a value in G7.
>
> Doing this manually, i end up with $31.95 in cell G7 ($1.3125 +
> $26.8125 + $3.825)
>
> Help
>
>
> --
> clandis
> ------------------------------------------------------------------------
> clandis's Profile:
> http://www.excelforum.com/member.php...o&userid=25468
> View this thread: http://www.excelforum.com/showthread...hreadid=389079
>

One way, for all your eBay needs:

=ROUND(SUMPRODUCT(--(G6>{0,25,1000}),(G6-{0,25,1000}),
{0.0525,-0.025,-0.0125}),2)

You can find other variations, including using a table of rates at

http://www.mcgimpsey.com/excel/variablerates.html



In article <[email protected]>,
clandis <[email protected]> wrote:

> If someone could offer a solution on a percentage formula based on the
> table below I would greatly appreciate it:
>
> if the value in cell G6 is $00.01 - $25.00, the percentage is 5.25%
> if the value in cell G6 is $25.01 - $1000.00, the percentage is 2.75%
> if the value in cell G6 is $1000.01 and greater, the percentage is
> 1.50%
>
> For example, if I have a value of $1,255.00 in cell G6, I need to
> calculate the first $25.00 at 5.25%, $975 at 2.75% and the remainder of
> $255.00 at 1.50% added together and output a value in G7.
>
> Doing this manually, i end up with $31.95 in cell G7 ($1.3125 +
> $26.8125 + $3.825)

Several options.

One from Chip Pearson:

http://www.cpearson.com/excel/pricing.htm

Another one is to use the User Defined Function below
Go to the VB Editor (ALT+F11)
Insert>Module
Paste the funcion in the code window
Use the function from your worksheet
' ===============================
Function PercPerSegment(Amount As Double, Table As Range) As Double
' Progressive pricing
' First argument is the quantity to be priced
' or the amount to be taxed
' Second argument is the Price or Tax% table (vertical)
' Make sure both ends of the table are correct;
' usually you start with zero and the corresponding price or %
' Any value should be found within the limits of the table, so
' if the top slice is infinite, then use
' something like 99999999999999999 as threshold
' and =NA() as corresponding value

Dim StillLeft As Double
Dim AmountThisSlice As Double
Dim SumSoFar As Double
Dim Counter As Long

StillLeft = Amount

For Counter = 1 To Table.Rows.Count - 1
AmountThisSlice = Application.Min(StillLeft, Table(Counter + 1, 1) _
- Table(Counter, 1))
SumSoFar = SumSoFar + AmountThisSlice * Table(Counter, 2)
StillLeft = StillLeft - AmountThisSlice
Next
PercPerSegment = SumSoFar
End Function
' ===============================

--
Kind regards,

Niek Otten

Microsoft MVP - Excel


"clandis" <[email protected]> wrote in
message news:[email protected]...
>
> If someone could offer a solution on a percentage formula based on the
> table below I would greatly appreciate it:
>
> if the value in cell G6 is $00.01 - $25.00, the percentage is 5.25%
> if the value in cell G6 is $25.01 - $1000.00, the percentage is 2.75%
> if the value in cell G6 is $1000.01 and greater, the percentage is
> 1.50%
>
> For example, if I have a value of $1,255.00 in cell G6, I need to
> calculate the first $25.00 at 5.25%, $975 at 2.75% and the remainder of
> $255.00 at 1.50% added together and output a value in G7.
>
> Doing this manually, i end up with $31.95 in cell G7 ($1.3125 +
> $26.8125 + $3.825)
>
> Help
>
>
> --
> clandis
> ------------------------------------------------------------------------
> clandis's Profile:
> http://www.excelforum.com/member.php...o&userid=25468
> View this thread: http://www.excelforum.com/showthread...hreadid=389079
>

=MIN(25,G6)*5.25%+(MIN(975,G6-25)*2.75%)*(G6>25)+MAX(0,G6-1000)*1.5%


"clandis" <[email protected]> wrote in
message news:[email protected]...
>
> If someone could offer a solution on a percentage formula based on the
> table below I would greatly appreciate it:
>
> if the value in cell G6 is $00.01 - $25.00, the percentage is 5.25%
> if the value in cell G6 is $25.01 - $1000.00, the percentage is 2.75%
> if the value in cell G6 is $1000.01 and greater, the percentage is
> 1.50%
>
> For example, if I have a value of $1,255.00 in cell G6, I need to
> calculate the first $25.00 at 5.25%, $975 at 2.75% and the remainder of
> $255.00 at 1.50% added together and output a value in G7.
>
> Doing this manually, i end up with $31.95 in cell G7 ($1.3125 +
> $26.8125 + $3.825)
>
> Help
>
>
> --
> clandis
> ------------------------------------------------------------------------
> clandis's Profile:
> http://www.excelforum.com/member.php...o&userid=25468
> View this thread: http://www.excelforum.com/showthread...hreadid=389079
>

Maybe a series of IF instructions would work. I can't write the formula for
you, but you would need three strings:

If value in G6 is >.01<25, the output is G6 times .0525
If value is G6 is >25<1000, output is 1.3125 plus (G6-25) times .0275
If value in G6 is >1000, output is 1.3125 plus 26.8125 plus (G6-1000) times
..015

"clandis" <[email protected]> wrote in
message news:[email protected]...
>
> If someone could offer a solution on a percentage formula based on the
> table below I would greatly appreciate it:
>
> if the value in cell G6 is $00.01 - $25.00, the percentage is 5.25%
> if the value in cell G6 is $25.01 - $1000.00, the percentage is 2.75%
> if the value in cell G6 is $1000.01 and greater, the percentage is
> 1.50%
>
> For example, if I have a value of $1,255.00 in cell G6, I need to
> calculate the first $25.00 at 5.25%, $975 at 2.75% and the remainder of
> $255.00 at 1.50% added together and output a value in G7.
>
> Doing this manually, i end up with $31.95 in cell G7 ($1.3125 +
> $26.8125 + $3.825)
>
> Help
>
>
> --
> clandis
> ------------------------------------------------------------------------
> clandis's Profile:
> http://www.excelforum.com/member.php...o&userid=25468
> View this thread: http://www.excelforum.com/showthread...hreadid=389079
>

Several options.

One from Chip Pearson:

http://www.cpearson.com/excel/pricing.htm

Another one is to use the User Defined Function below
Go to the VB Editor (ALT+F11)
Insert>Module
Paste the funcion in the code window
Use the function from your worksheet
' ===============================
Function PercPerSegment(Amount As Double, Table As Range) As Double
' Progressive pricing
' First argument is the quantity to be priced
' or the amount to be taxed
' Second argument is the Price or Tax% table (vertical)
' Make sure both ends of the table are correct;
' usually you start with zero and the corresponding price or %
' Any value should be found within the limits of the table, so
' if the top slice is infinite, then use
' something like 99999999999999999 as threshold
' and =NA() as corresponding value

Dim StillLeft As Double
Dim AmountThisSlice As Double
Dim SumSoFar As Double
Dim Counter As Long

StillLeft = Amount

For Counter = 1 To Table.Rows.Count - 1
AmountThisSlice = Application.Min(StillLeft, Table(Counter + 1, 1) _
- Table(Counter, 1))
SumSoFar = SumSoFar + AmountThisSlice * Table(Counter, 2)
StillLeft = StillLeft - AmountThisSlice
Next
PercPerSegment = SumSoFar
End Function
' ===============================

--
Kind regards,

Niek Otten

Microsoft MVP - Excel


"clandis" <[email protected]> wrote in
message news:[email protected]...
>
> If someone could offer a solution on a percentage formula based on the
> table below I would greatly appreciate it:
>
> if the value in cell G6 is $00.01 - $25.00, the percentage is 5.25%
> if the value in cell G6 is $25.01 - $1000.00, the percentage is 2.75%
> if the value in cell G6 is $1000.01 and greater, the percentage is
> 1.50%
>
> For example, if I have a value of $1,255.00 in cell G6, I need to
> calculate the first $25.00 at 5.25%, $975 at 2.75% and the remainder of
> $255.00 at 1.50% added together and output a value in G7.
>
> Doing this manually, i end up with $31.95 in cell G7 ($1.3125 +
> $26.8125 + $3.825)
>
> Help
>
>
> --
> clandis
> ------------------------------------------------------------------------
> clandis's Profile:
> http://www.excelforum.com/member.php...o&userid=25468
> View this thread: http://www.excelforum.com/showthread...hreadid=389079
>

=MIN(25,G6)*5.25%+(MIN(975,G6-25)*2.75%)*(G6>25)+MAX(0,G6-1000)*1.5%


"clandis" <[email protected]> wrote in
message news:[email protected]...
>
> If someone could offer a solution on a percentage formula based on the
> table below I would greatly appreciate it:
>
> if the value in cell G6 is $00.01 - $25.00, the percentage is 5.25%
> if the value in cell G6 is $25.01 - $1000.00, the percentage is 2.75%
> if the value in cell G6 is $1000.01 and greater, the percentage is
> 1.50%
>
> For example, if I have a value of $1,255.00 in cell G6, I need to
> calculate the first $25.00 at 5.25%, $975 at 2.75% and the remainder of
> $255.00 at 1.50% added together and output a value in G7.
>
> Doing this manually, i end up with $31.95 in cell G7 ($1.3125 +
> $26.8125 + $3.825)
>
> Help
>
>
> --
> clandis
> ------------------------------------------------------------------------
> clandis's Profile:
> http://www.excelforum.com/member.php...o&userid=25468
> View this thread: http://www.excelforum.com/showthread...hreadid=389079
>

One way, for all your eBay needs:

=ROUND(SUMPRODUCT(--(G6>{0,25,1000}),(G6-{0,25,1000}),
{0.0525,-0.025,-0.0125}),2)

You can find other variations, including using a table of rates at

http://www.mcgimpsey.com/excel/variablerates.html



In article <[email protected]>,
clandis <[email protected]> wrote:

> If someone could offer a solution on a percentage formula based on the
> table below I would greatly appreciate it:
>
> if the value in cell G6 is $00.01 - $25.00, the percentage is 5.25%
> if the value in cell G6 is $25.01 - $1000.00, the percentage is 2.75%
> if the value in cell G6 is $1000.01 and greater, the percentage is
> 1.50%
>
> For example, if I have a value of $1,255.00 in cell G6, I need to
> calculate the first $25.00 at 5.25%, $975 at 2.75% and the remainder of
> $255.00 at 1.50% added together and output a value in G7.
>
> Doing this manually, i end up with $31.95 in cell G7 ($1.3125 +
> $26.8125 + $3.825)

=MIN(25,G6)*5.25%+(MIN(975,G6-25)*2.75%)*(G6>25)+MAX(0,G6-1000)*1.5%


"clandis" <[email protected]> wrote in
message news:[email protected]...
>
> If someone could offer a solution on a percentage formula based on the
> table below I would greatly appreciate it:
>
> if the value in cell G6 is $00.01 - $25.00, the percentage is 5.25%
> if the value in cell G6 is $25.01 - $1000.00, the percentage is 2.75%
> if the value in cell G6 is $1000.01 and greater, the percentage is
> 1.50%
>
> For example, if I have a value of $1,255.00 in cell G6, I need to
> calculate the first $25.00 at 5.25%, $975 at 2.75% and the remainder of
> $255.00 at 1.50% added together and output a value in G7.
>
> Doing this manually, i end up with $31.95 in cell G7 ($1.3125 +
> $26.8125 + $3.825)
>
> Help
>
>
> --
> clandis
> ------------------------------------------------------------------------
> clandis's Profile:
> http://www.excelforum.com/member.php...o&userid=25468
> View this thread: http://www.excelforum.com/showthread...hreadid=389079
>

One way, for all your eBay needs:

=ROUND(SUMPRODUCT(--(G6>{0,25,1000}),(G6-{0,25,1000}),
{0.0525,-0.025,-0.0125}),2)

You can find other variations, including using a table of rates at

http://www.mcgimpsey.com/excel/variablerates.html



In article <[email protected]>,
clandis <[email protected]> wrote:

> If someone could offer a solution on a percentage formula based on the
> table below I would greatly appreciate it:
>
> if the value in cell G6 is $00.01 - $25.00, the percentage is 5.25%
> if the value in cell G6 is $25.01 - $1000.00, the percentage is 2.75%
> if the value in cell G6 is $1000.01 and greater, the percentage is
> 1.50%
>
> For example, if I have a value of $1,255.00 in cell G6, I need to
> calculate the first $25.00 at 5.25%, $975 at 2.75% and the remainder of
> $255.00 at 1.50% added together and output a value in G7.
>
> Doing this manually, i end up with $31.95 in cell G7 ($1.3125 +
> $26.8125 + $3.825)

Several options.

One from Chip Pearson:

http://www.cpearson.com/excel/pricing.htm

Another one is to use the User Defined Function below
Go to the VB Editor (ALT+F11)
Insert>Module
Paste the funcion in the code window
Use the function from your worksheet
' ===============================
Function PercPerSegment(Amount As Double, Table As Range) As Double
' Progressive pricing
' First argument is the quantity to be priced
' or the amount to be taxed
' Second argument is the Price or Tax% table (vertical)
' Make sure both ends of the table are correct;
' usually you start with zero and the corresponding price or %
' Any value should be found within the limits of the table, so
' if the top slice is infinite, then use
' something like 99999999999999999 as threshold
' and =NA() as corresponding value

Dim StillLeft As Double
Dim AmountThisSlice As Double
Dim SumSoFar As Double
Dim Counter As Long

StillLeft = Amount

For Counter = 1 To Table.Rows.Count - 1
AmountThisSlice = Application.Min(StillLeft, Table(Counter + 1, 1) _
- Table(Counter, 1))
SumSoFar = SumSoFar + AmountThisSlice * Table(Counter, 2)
StillLeft = StillLeft - AmountThisSlice
Next
PercPerSegment = SumSoFar
End Function
' ===============================

--
Kind regards,

Niek Otten

Microsoft MVP - Excel


"clandis" <[email protected]> wrote in
message news:[email protected]...
>
> If someone could offer a solution on a percentage formula based on the
> table below I would greatly appreciate it:
>
> if the value in cell G6 is $00.01 - $25.00, the percentage is 5.25%
> if the value in cell G6 is $25.01 - $1000.00, the percentage is 2.75%
> if the value in cell G6 is $1000.01 and greater, the percentage is
> 1.50%
>
> For example, if I have a value of $1,255.00 in cell G6, I need to
> calculate the first $25.00 at 5.25%, $975 at 2.75% and the remainder of
> $255.00 at 1.50% added together and output a value in G7.
>
> Doing this manually, i end up with $31.95 in cell G7 ($1.3125 +
> $26.8125 + $3.825)
>
> Help
>
>
> --
> clandis
> ------------------------------------------------------------------------
> clandis's Profile:
> http://www.excelforum.com/member.php...o&userid=25468
> View this thread: http://www.excelforum.com/showthread...hreadid=389079
>

Maybe a series of IF instructions would work. I can't write the formula for
you, but you would need three strings:

If value in G6 is >.01<25, the output is G6 times .0525
If value is G6 is >25<1000, output is 1.3125 plus (G6-25) times .0275
If value in G6 is >1000, output is 1.3125 plus 26.8125 plus (G6-1000) times
..015

"clandis" <[email protected]> wrote in
message news:[email protected]...
>
> If someone could offer a solution on a percentage formula based on the
> table below I would greatly appreciate it:
>
> if the value in cell G6 is $00.01 - $25.00, the percentage is 5.25%
> if the value in cell G6 is $25.01 - $1000.00, the percentage is 2.75%
> if the value in cell G6 is $1000.01 and greater, the percentage is
> 1.50%
>
> For example, if I have a value of $1,255.00 in cell G6, I need to
> calculate the first $25.00 at 5.25%, $975 at 2.75% and the remainder of
> $255.00 at 1.50% added together and output a value in G7.
>
> Doing this manually, i end up with $31.95 in cell G7 ($1.3125 +
> $26.8125 + $3.825)
>
> Help
>
>
> --
> clandis
> ------------------------------------------------------------------------
> clandis's Profile:
> http://www.excelforum.com/member.php...o&userid=25468
> View this thread: http://www.excelforum.com/showthread...hreadid=389079
>

=MIN(25,G6)*5.25%+(MIN(975,G6-25)*2.75%)*(G6>25)+MAX(0,G6-1000)*1.5%


"clandis" <[email protected]> wrote in
message news:[email protected]...
>
> If someone could offer a solution on a percentage formula based on the
> table below I would greatly appreciate it:
>
> if the value in cell G6 is $00.01 - $25.00, the percentage is 5.25%
> if the value in cell G6 is $25.01 - $1000.00, the percentage is 2.75%
> if the value in cell G6 is $1000.01 and greater, the percentage is
> 1.50%
>
> For example, if I have a value of $1,255.00 in cell G6, I need to
> calculate the first $25.00 at 5.25%, $975 at 2.75% and the remainder of
> $255.00 at 1.50% added together and output a value in G7.
>
> Doing this manually, i end up with $31.95 in cell G7 ($1.3125 +
> $26.8125 + $3.825)
>
> Help
>
>
> --
> clandis
> ------------------------------------------------------------------------
> clandis's Profile:
> http://www.excelforum.com/member.php...o&userid=25468
> View this thread: http://www.excelforum.com/showthread...hreadid=389079
>

Several options.

One from Chip Pearson:

http://www.cpearson.com/excel/pricing.htm

Another one is to use the User Defined Function below
Go to the VB Editor (ALT+F11)
Insert>Module
Paste the funcion in the code window
Use the function from your worksheet
' ===============================
Function PercPerSegment(Amount As Double, Table As Range) As Double
' Progressive pricing
' First argument is the quantity to be priced
' or the amount to be taxed
' Second argument is the Price or Tax% table (vertical)
' Make sure both ends of the table are correct;
' usually you start with zero and the corresponding price or %
' Any value should be found within the limits of the table, so
' if the top slice is infinite, then use
' something like 99999999999999999 as threshold
' and =NA() as corresponding value

Dim StillLeft As Double
Dim AmountThisSlice As Double
Dim SumSoFar As Double
Dim Counter As Long

StillLeft = Amount

For Counter = 1 To Table.Rows.Count - 1
AmountThisSlice = Application.Min(StillLeft, Table(Counter + 1, 1) _
- Table(Counter, 1))
SumSoFar = SumSoFar + AmountThisSlice * Table(Counter, 2)
StillLeft = StillLeft - AmountThisSlice
Next
PercPerSegment = SumSoFar
End Function
' ===============================

--
Kind regards,

Niek Otten

Microsoft MVP - Excel


"clandis" <[email protected]> wrote in
message news:[email protected]...
>
> If someone could offer a solution on a percentage formula based on the
> table below I would greatly appreciate it:
>
> if the value in cell G6 is $00.01 - $25.00, the percentage is 5.25%
> if the value in cell G6 is $25.01 - $1000.00, the percentage is 2.75%
> if the value in cell G6 is $1000.01 and greater, the percentage is
> 1.50%
>
> For example, if I have a value of $1,255.00 in cell G6, I need to
> calculate the first $25.00 at 5.25%, $975 at 2.75% and the remainder of
> $255.00 at 1.50% added together and output a value in G7.
>
> Doing this manually, i end up with $31.95 in cell G7 ($1.3125 +
> $26.8125 + $3.825)
>
> Help
>
>
> --
> clandis
> ------------------------------------------------------------------------
> clandis's Profile:
> http://www.excelforum.com/member.php...o&userid=25468
> View this thread: http://www.excelforum.com/showthread...hreadid=389079
>

One way, for all your eBay needs:

=ROUND(SUMPRODUCT(--(G6>{0,25,1000}),(G6-{0,25,1000}),
{0.0525,-0.025,-0.0125}),2)

You can find other variations, including using a table of rates at

http://www.mcgimpsey.com/excel/variablerates.html



In article <[email protected]>,
clandis <[email protected]> wrote:

> If someone could offer a solution on a percentage formula based on the
> table below I would greatly appreciate it:
>
> if the value in cell G6 is $00.01 - $25.00, the percentage is 5.25%
> if the value in cell G6 is $25.01 - $1000.00, the percentage is 2.75%
> if the value in cell G6 is $1000.01 and greater, the percentage is
> 1.50%
>
> For example, if I have a value of $1,255.00 in cell G6, I need to
> calculate the first $25.00 at 5.25%, $975 at 2.75% and the remainder of
> $255.00 at 1.50% added together and output a value in G7.
>
> Doing this manually, i end up with $31.95 in cell G7 ($1.3125 +
> $26.8125 + $3.825)

Maybe a series of IF instructions would work. I can't write the formula for
you, but you would need three strings:

If value in G6 is >.01<25, the output is G6 times .0525
If value is G6 is >25<1000, output is 1.3125 plus (G6-25) times .0275
If value in G6 is >1000, output is 1.3125 plus 26.8125 plus (G6-1000) times
..015

"clandis" <[email protected]> wrote in
message news:[email protected]...
>
> If someone could offer a solution on a percentage formula based on the
> table below I would greatly appreciate it:
>
> if the value in cell G6 is $00.01 - $25.00, the percentage is 5.25%
> if the value in cell G6 is $25.01 - $1000.00, the percentage is 2.75%
> if the value in cell G6 is $1000.01 and greater, the percentage is
> 1.50%
>
> For example, if I have a value of $1,255.00 in cell G6, I need to
> calculate the first $25.00 at 5.25%, $975 at 2.75% and the remainder of
> $255.00 at 1.50% added together and output a value in G7.
>
> Doing this manually, i end up with $31.95 in cell G7 ($1.3125 +
> $26.8125 + $3.825)
>
> Help
>
>
> --
> clandis
> ------------------------------------------------------------------------
> clandis's Profile:
> http://www.excelforum.com/member.php...o&userid=25468
> View this thread: http://www.excelforum.com/showthread...hreadid=389079
>