On Sep 2, 12:47 pm, "Ed" wrote:
Thank you for all your help.

I actually would like to exact percentage (not rounded off). I should've
made it more clear. Yes, there are 24 percent groups.

What would be the formula for a long series of IF statements? Also, is
there a limitation to the number of IF statements that you can include
within a formula (24 percent groups).

Thank you.

Ed.

"Spiky" wrote in message

...

On Sep 1, 2:42 pm, "Ed" wrote:
I found the following formula, using interpolation. It will start by
assigning a 50% markup to $1.00 and the percentage will go down from
there
until it reaches $39.99; and will start at 12% for anything above $40.00.

=IF(A1<=39.99,(-A1/100+0.50+1/100),0.12)

I would like to know how I can add to the formula so that it starts and
ends
at 14 different intervals:

Did you mean 24 percent groups since you listed 24? The ones you
listed are not linear or predictable in any mathematical way, so one
formula won't cut it, you'll need several to get all those different
percents. BTW, that formula will not give you exactly the right
amount, you'd need to add a rounding. IE: $7.50 cost equals a percent
of 43.5, not 44 even. So:
=IF(A1<=39.99,ROUND(-A1/100+0.50+1/100,2),0.12)

However, unless you want a long series of IF statements for a formula,
why not use a lookup table? Put the table on a separate worksheet, out
of the way of your invoice. Or use the "table within a formula" as
shown in Gord's link, but in a LOOKUP function:
=LOOKUP(A1,{1;2;3;4,etc},{0.5;0.49;0.48;0.47,etc})

For an actual table:
You can just enter the table like you have typed here (you would need
to take out the ranges, just use $1,2,3...), and use the Index/Match
formula I gave for a lookup. Although I designed it with the highest
cost number at the top. So:

40.......12%
35.......14%
30.......16%
etc


You can have only 7 nested IF statements, I believe. You don't
actually need 24, though. In looking at your list, I see you can use
your above formula for everything from $1.00-15.99. $16.00-18.99 is
also a steady range, so if you can alter your calculation to jump 2%
each $1, you should be able to stay within the 7 IF requirement.
Continuing like this, I see 5 sections. Something like this:

=IF(A1<16,(-A1/100+0.51),IF(A1<19,(2nd calc),IF(A1<25,(3rd
calc),IF(A1<40,(4th calc),0.12)

Sorry, my brain isn't working well on figuring out those formulas
offhand. Calculate those formulas for each section and plug them in.