Spread Sheet Help again

**califman1961** · 02-18-2005, 02:35 PM

Here it is hope someone can help me out one more time?

I sell something for $25.00 or lower the fee is 5.25% ($1.31), But if its more $25.01 to $1,000.00 fee is 5.25% of the initial $25.00 ($1.31), plus 2.75% of the remaining closing value balance ($25.01 to $1,000.00)

Cell H1 is $25.00 then Cell J1 is $1.31 (5.25%)
Cell H1 is $25.01 then Cell J1 is $1.31 (5.25%)plus 2.75%
Can I have them in the same cell somehow is this possable?
Thanks for the help

**BenjieLop** · 02-18-2005, 04:16 PM

Originally Posted by califman1961

Here it is hope someone can help me out one more time?

I sell something for $25.00 or lower the fee is 5.25% ($1.31), But if its more $25.01 to $1,000.00 fee is 5.25% of the initial $25.00 ($1.31), plus 2.75% of the remaining closing value balance ($25.01 to $1,000.00)

Cell H1 is $25.00 then Cell J1 is $1.31 (5.25%)
Cell H1 is $25.01 then Cell J1 is $1.31 (5.25%)plus 2.75%
Can I have them in the same cell somehow is this possable?
Thanks for the help

Enter this in Cell J1 :

=if(H1<=25,H1*5.25%,1.31+(H1-25)*2.75%)

The above formula covers sales even above $1000 though. If you need another percentage for sales over $1000, just post back.

Regards.

JE McGimpsey · 02-18-2005, 05:06 PM

One way:

=ROUND(SUMPRODUCT(--(H1>{0,25}),(H1-{0,25}),{0.0525,-0.025}),2)

See

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

for an explanation of the formula, and a way to extend it beyond $1,000.

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

> Here it is hope someone can help me out one more time?
>
> I sell something for $25.00 or lower the fee is 5.25% ($1.31), But if
> its more $25.01 to $1,000.00 fee is 5.25% of the initial $25.00
> ($1.31), plus 2.75% ($0.68)of the remaining closing value balance
> ($25.01 to $1,000.00)
>
> Cell H1 is $25.00 then Cell J1 is $1.31 (5.25%)
> Cell H1 is $25.01 then Cell J1 is $1.31 (5.25%)plus 2.75% or $1.99
> Can I have them in the same cell somehow is this possable?
> Thanks for the help