formula help

[Aaron]

Hi,

I'm trying to make myself a handy worksheet to figure mark up on the items
that I sell. I want to make it so that there is only one box to enter a cost
of an item into, and then based on the dollar amount, the selling price is
calculated and displayed in another window. The problem that I'm having is
that I don't know how to, or if it's even possible, to assign perameters into
one formula. So, if this isn't making any sense, here's my chart that I'm
working from:

cost value multiply by
$.01-5.00 3
$5.01-15 2
$15.01-30 1.5
ETC...

So, if I have an item that costs me $4.00, I want to multiply that item by 3
to get my selling price. But if an item costs me $6.00, I only want to
multiply that value by 2.

Hopefully this can be done so that I can just have one box that I enter a
cost amount into to get my correct selling price instead of having to pull
out a calculator every time I need to do this.

A response is much appreciated.

Aaron

 

[AndyLV]

=IF(A1<5.01,A1*3,IF(A1<15.01,A1*2,IF(A1<30.01,A1*1.5,"")))

 

[David Biddulph]

=IF(A2<=5,A2*3,IF(A2<=15,A2*2,IF(A2<=30,1.5,"undefined")))

If your "ETC..." stretches the nested IF statement to more than 7 levels of
nesting, you may wanted to look at VLOOKUP instead of nested IFs.

You will realise, I hope, that this means that you will sell something
costing $5.01 at a lower selling price than something costing $5, and taking
your 2 examples, the selling price for your $4 and $6 items will be the same
(both $12)?

 

[Aaron]

I do realize what you're saying. I didn't say it was my system. lol It's
just what I have to work with.

That's funny about my examples. I didn't realize until you said something
that they would equal the same sales price. I was just pulling numbers out of
my ars.

Can you expand on VLOOKUP? My etc. does extend to about 11 lines.

Thanks for your insight. Appreciated. I'm trying to get a better grasp on
Excel.

=IF(A2<=5,A2*3,IF(A2<=15,A2*2,IF(A2<=30,1.5,"undefined")))

 

[David Biddulph]

VLOOKUP is a standard Excel function, so start by looking it up in Excel
help.