formula help

A

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
 
D

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)?
 
A

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.

David Biddulph said:
=IF(A2<=5,A2*3,IF(A2<=15,A2*2,IF(A2<=30,1.5,"undefined")))
 
D

David Biddulph

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

relective formula 0
making a pricing matrix 3
Calculate Margins 3
Mail Merge Help 1
Help with calculation 2
calulation 1
Excel formulas 2
Margin Formula 2

Top