Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel Formula-Figuring Sell Price based on Cost 4

Status
Not open for further replies.
rcrecelius

rcrecelius

Technical User
Mar 13, 2003
108
0
0
US
I have a SS with over 16000 part numbers and our cost. I want to calculate our selling price based on the cost of each item. Ex...
if the item costs less than 2.00, markup 45%(/.55)
2.01-3.00 markup 40%(/.6)
3.01-4.00 markup 34%(/.66)
The percentages vary on up from there but you get the picture.
I could do this by creating other columns but I would like to know if this is possible in a single formula?

 
Sort by date Sort by votes
You should be able to do that with a nested if as in:

if(costcell<=2.00,costcell*45%,if(costcell<=3.00,costcell*40%,...)

You just need a cost cell, obviously. The If formula would go into the price cell.

The IF formula is IF(expression, if true, if false).

Hope that helps.


DreamerZ
simplesolutions04@sbcglobal.net
[ignore][/ignore]
 
Upvote 0 Downvote
Hi,

Pretty simple formula
if the item costs less than 2.00, markup 45%(/.55)
2.01-3.00 markup 40%(/.6)
3.01-4.00 markup 34%(/.66)
[tt]
=Item_Cost/IF(Item_Cost<=2,0.55,IF(Item_Cost<=3,0.6,0.66))
[/tt]
I would also store each break point & markup divisor in single location and name each one. This has 2 advantages. 1) if there are changes in either the break point or markup divisor, you change ONE value. 2) Named ranges are easier to understand. so it helps in the maintenance of the sheet.

So it might look like this
[tt]
=Item_Cost/IF(Item_Cost<=Val1,Mrk1,IF(Item_Cost<=Val2,Mrk2,Mrk3))
[/tt]
where then named ranges have corresponding values...
[tt]
Val1: 2 Mrk1: 0.55
Val2: 3 Mrk2: 0.60
Val3: 4 Mrk3: 0.66
[/tt]
:)



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote
You can do this with the IF formula above, but what happens when your percentages change a little bit, or the range changes a bit? I would suggest creating a little table with your price ranges in one column and the percentages in the next column. Then you can use a VLOOKUP formula on the price to get the percentage to multiply with. Then, if your ranges or percentages change, all you need to do is adjust the table accordingly. For example:

Table is E1 through F 20 with Col E containing price ranges from low to high and Col F containing the percentages.

In Col A is the part number, Col B the Cost, in Col C put:

=B1+(B1*VLOOKUP(B1,E1:F20,2,1))



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...
 
Upvote 0 Downvote
Thanks Skip, I am still popping in when I can :)



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...
 
Upvote 0 Downvote
Thanks for the replies gentlemen...
it looks like I will have 9 different values/markups. I was under the impression that you could only have 7 nested functions in a formula?
Its also possible that I dont fully understand "nested functions".
 
Upvote 0 Downvote
Thanks...I was replying before I saw Blues posts.
 
Upvote 0 Downvote
And, for future use rcrelius, there are ways to get around the 7 IF limitation like the following:

=VALUE(IF(A1="A",1,"")&IF(A1="B",2,"")&IF(A1="C",3,"")&IF(A1="D",4,"")&IF(A1="E",5,"")&IF(A1="F",6,"")&IF(A1="G",7,"")&IF(A1="H",8,"")&IF(A1="I",9,""))



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...
 
Upvote 0 Downvote
(VLOOKUP(D2,Sheet2!A2:B11,2,1))
This works great for the first couple of rows but the lookup array changes as I autofill down the sheet.
How do you make the lookup array Sheet2!A2:B11 remain the same?
 
Upvote 0 Downvote
Lock it with $ signs:-

VLOOKUP(D2,Sheet2!$A$2:$B$11,2,1))

Regards
Ken..............

----------------------------------------------------------------------------
[peace]It's easier to beg forgiveness than ask permission[2thumbsup]

----------------------------------------------------------------------------
 
Upvote 0 Downvote
WHY DO I ALWAYS FORGET TO INCORPORATE THE $...

Somebody slap me...



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...
 
Upvote 0 Downvote
[Slapping]

Blue

[/Slapping]

:)

----------------------------------------------------------------------------
[peace]It's easier to beg forgiveness than ask permission[2thumbsup]

----------------------------------------------------------------------------
 
Upvote 0 Downvote
Ok you slap happy guys...
Working good except for items that cost under 2.00, everything else is dead on.
I have my table set up like this...
Value Markup
0.00 .55
2.01 .6
3.01 .64
4.01 .66
5.01 .68

I use .55 to figure 45% gross profit so my end formula is...
D2/(VLOOKUP(D2,Sheet2!$A$2:$B$11,2,1))
 
Upvote 0 Downvote
Oh crap, I found the problem...A1 instead of A2...Doh!
thanks for all the help
 
Upvote 0 Downvote
I might just be missing something here, but going back to your first post, are you treating /0.6 as the same as marking up by 40%?

If I had a $100 and I marked it up by 40% I would expect to get $140 at sell.

If I had a $100 and I divided it by 0.6 I would get $167 at sell.

$100 at 0.5 is an easier example, where dividing by 0.5 doubles the cost whereas a 50% markup would give you $150

Regards
Ken...........


----------------------------------------------------------------------------
[peace]It's easier to beg forgiveness than ask permission[2thumbsup]

----------------------------------------------------------------------------
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

arvarr
  • Locked
  • Question
Excel Formula Help 1
Replies
7
Views
153
SkipVought
S
TomSalvato
  • Locked
  • Question
EXCEL-formula help 2
Replies
6
Views
102
TomSalvato
TomSalvato
Bijugate
  • Question
Excel update question
Replies
0
Views
105
Bijugate
Bijugate
Pirouette
  • Locked
  • Question
Excel workbook
Replies
1
Views
133
combo
combo
M
  • Locked
  • Question
Excel Power Query
Replies
4
Views
136
combo
combo

Part and Inventory Search

Sponsor

Back
Top