Formulate a markup dependent on cell value?

[bullethvac]

Hi,
I need help with a formula if possible. I have a spreadsheet that I use to price products for construction bidding purposes. My parts have different markups dependent on their cost. Is there a formula that can automatically change the markup calculation based on the ranges of the cost?

example: if I were to enter $30.00 in C3, I would want C5 to show a markup of 3.5 and C7 to show the sell price of $105.00
if I were to enter $255.00 in C3, I would want C5 to show a markup of 2.0 and C7 to show the sell price of $510.00
etc.

Thank you in advance!

 

[bullethvac]

I failed to mention, the markups run like this: 0.00-99.99 = 4 - 100.00-249.99=3.5 etc.

 

[etaf]

do you have a list of the mark values based on the cost ?
then you could use a lookup - nearest low value

 

[etaf]

i suspect there is a simple maths way to do - but heres a lookup
not sure what etc means, for the values - but the table can be extended and the index/match range changed to suite

also change the cells so
But your 2nd post does not seem to match the first requirement

$30.00 in C3, I would want C5 to show a markup of 3.5 and C7

BUT

0.00-99.99 = 4

Book17
	A	B	C	D	E
1
2	50	4
3	100	3.5		0	4
4	150	3.5		100	3.5
5	200	3.5		250	3
6	99.99999	4
Sheet1

Cell Formulas
Range	Formula
B2:B6	B2	=INDEX($E$3:$E$5,MATCH(A2,$D$3:$D$5,1))

Book17
	A	B	C	D	E	F
1
2
3			300		0	4
4					100	3.5
5			3		250	3
6
7			900
8
Sheet1

Cell Formulas
Range	Formula
C5	C5	=INDEX($F$3:$F$5,MATCH(C3,$E$3:$E$5,1))
C7	C7	=C3*C5

 

[bullethvac]

yes, where would the list go?
$0-$25.00 4.0
$25.01-$50.00 3.5
$50.02-$100.00 3.0
etc

I am very elementary in my excel skills, what would be the lookup formula?

 

[etaf]

anywhere it works on the spreadsheet - even in a different sheet to keep it out the way

Note - I edited
as didnt quite match

where can you add a table ? as shown

 

[bullethvac]

	2	=INDEX($3:$E$5,MATCH(A2,$D$3:$D

This is the sheet I am working on. I can put the table on worksheet 2, but I am not sure how to get the cell with the INDEX formula to look on sheet 2 for the values. I appreciate your help, sorry I am not fluent with excel!

 

[bullethvac]

I was able to get your formula and table working as it should on my sheet. It took changing the referenced cells etc. to get it going (of course). I am unsure of how to put that table on worksheet 2 and have that formula reference it correctly.

 

[etaf]

how about

Book17
	A	B	C	D
1
2
3			300
4
5			3
6
7			900
8
Sheet1

Cell Formulas
Range	Formula
C5	C5	=INDEX(Sheet2!$B$2:$B$10,MATCH(C3,Sheet2!$A$2:$A$10,1))
C7	C7	=C3*C5

Book17
	A	B
1	Amount	Rate
2	25	4
3	50	3.5
4	100	3
Sheet2

on dropbox - BUT only for a few days, then i delete off

rate-ETAF.xlsx

Shared with Dropbox

www.dropbox.com

 

[bullethvac]

Thank you! It works great and the table is hidden. After having to read through your formula and figure out where it was pointing and why, I fully understand how it works!