calculating fee percentages

[*K*]

HI all, hope someone can help me...........

I want to calculate a fee based on a percentage

fee schedule is

Minimum fee 45c
Sale price $150 and under 5.5% of sale price
Sale price between $150 and $1500 $8.25 + 3.75% of sale price over
$150
Sale price over $1500 $58.88 + 2% of sale price over $1500
Maximum fee $199

can someone hlep please

I have 3 cells with sale price, fee and of course total I want formula
for cell B to calculate fee based on sale price.

TIA
K

 

[JulieD]

Hi K

here is rather a long winded formula so that you can see how it is
constructed (i also took the liberty of rounding the fee to 2 decimal
places)
=ROUND(IF(A3<=150,MAX(A3*5.5%,0.45),IF(A3<=1500,8.25+((A3-150)*3.75%),IF(A3>1500,MIN(58.88+((A3-1500)*2%),199),0))),2)

Hope this helps
Cheers
JulieD

 

[Max]

Just another option to try out ..

In Sheet1
-----------
Setup the table below in A2:C4

0____5.50%__0
151__3.75%__8.25
1501_2.00%__58.88

(Above assumes sales price will have a minimum increment of $1)

Name the range A2:C4 as : FeeTable

To name, just select the range, then
click inside the namebox (just to the left of the formula bar)
and key-in: FeeTable. Press Enter.

Put in 2 cells, say
in B6: 0.45
in B7: 199
Name the cells B6 and B7 as: MinFee and MaxFee

In Sheet2
-----------
Assume you have the sales price listed in A2 down

Put in B2:

=IF(A2="","",IF(A2*VLOOKUP(A2,FeeTable,2)+VLOOKUP(A2,FeeTable,3)<MinFee,MinF
ee,IF(A2*VLOOKUP(A2,FeeTable,2)+VLOOKUP(A2,FeeTable,3)>MaxFee,MaxFee,A2*VLOO
KUP(A2,FeeTable,2)+VLOOKUP(A2,FeeTable,3))))

Copy B2 down

Col B will compute the required fee

 

[W. D. Allen Sr.]

Hello Julie,

I am curious as to how one goes about writing Excel single cell functions as
long and involved as what you have show below. Do you start at the left and
work to the right, start in the middle and work both ways, or whatever? Can
you recommend a good book on writing such complex functions?

Thanks for your kind consideration.

WDA

(e-mail address removed)

end

 

[*K*]

Hi K

here is rather a long winded formula so that you can see how it is
constructed (i also took the liberty of rounding the fee to 2 decimal
places)
=ROUND(IF(A3<=150,MAX(A3*5.5%,0.45),IF(A3<=1500,8.25+((A3-150)*3.75%),IF(A3>1500,MIN(58.88+((A3-1500)*2%),199),0))),2)

Hope this helps
Cheers
JulieD

Certainly seems to work OK, thanks for that, much appreciated, I dont
understand any of it of course, but as I said it WORKS, so thanks.

K

 

[Trevor Shuttleworth]

WDA

I think it comes with practice ... and a bit of thought and planning. You
need to think through the conditions, the order you want to check them and
the result you want. In this case, you'd probably start with the IF
condition, building from the left to the right and checking the values in
ascending order. So you'd compare the cell to the first limit; if it is
true, you're done and you apply the first calculation; if it's not, you move
on to the next limit until you get to the end. Close off with matching
brackets. Then you apply the embellishments; in this case, the ROUND to 2
decimal places.

If you have too many conditions, you might not be able to use IF and would
perhap set up a LOOKUP table as, for example, the alternative solution.

Debra Dalgleish has an excellent book list on her Contextures site.

http://www.contextures.on.ca/xlbooks.html

Excel 2003 Formulas by John Walkenbach is probably a good one. A little
expensive maybe but it will cover all versions of Excel. Check Amazon for a
better price. I say "probably" because I've read earlier versions but not
this ... probably a good one for my Christmas list.

Regards

Trevor

 

[JulieD]

Hi

just following on Trevor's comments ... i've got John Walkenbach's Excel
2003 formulas book (and his programming one) and i think they're definitely
worth the money. However, Debra Dalgleish's site
(www.contextures.com/tiptech.html) and Chip Pearson's (www.cpearson.com) are
two places i have also learnt a lot about constructing formulas (and, of
course, these newsgroups).

With an IF statement if i know the number of variations and they're under 4
then i start with the lowest and work upwards ... if, however, they're over
4 then i start in the middle as you can only nest 7 IF functions together,
and then as Trevor said (re the ROUND bit) you do that last to make it look
pretty.

FYI here's a breakdown of the formula i gave you
four functions were used:
ROUND(thing to round, number of significant digits)
-here i wanted to round the whole thing to 2 significant digits
IF(test, true, false)
-this is the primary function to use when you want to return different
values based on whether or not a statement is true or false. You can nest
up to 7 IFs in both the true & false section of the first IF
=IF(test,IF(test,true,false),IF(test,true,false)) - structure of an IF with
another IF in the true bit & one in the false bit
MAX(value1,value2)
-returns the maximum from two or more values ... i used this as you said the
minimum charge was .45 so i had to say, if the fee works out less than that
return .45, or if it is more then return that.
MIN(value1,value2)
-returns the minimum from two or more values ... like the MAX one, i used
this to ensure that the fee was the maximum due OR 199, which ever was the
smallest.

the only tricky bit them was dealing with the different criterias in the IF
statement - and this boils down to the following structure in the case of
your formula
=IF(test,true,IF(test,true,IF(test,true,false)))
all nested in the ROUND function
=ROUND(IF(test,true,IF(test,true,IF(test,true,false))),2)

Hope this helps explain it.

Cheers
JulieD

 

[JE McGimpsey]

Just one more option:


=MAX(0.45,MIN(199,ROUND(SUMPRODUCT(--(A1>{0,150,1500}),(A1-{0,150,1500}),
{0.055,-0.0175,-0.0175}),2)))

You can get an explanation of the technique at

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