What formula can I use for this?

[lgarcia2509]

I’m working with a pricebook that has a row of pay rates ($20-$45) and includes columns of taxes under each pay rate all sectioned out by a specific profit margin percentage. I am trying to create a "rate card” on a separate sheet where you can type in any “pay rate” and it will calculate the value including taxes based off the percentage selected in a dropdown list (I’ve created on the rate card).

What formula can I use for this?

 

[lgarcia2509]

What I'm being asked is if I can create one cell in sheet1 where we can type in any pay rate and populate the corresponding bill rate, OT rate, holiday rate, and holiday OT rate based off a percentage selected in the drop-down (sheet1, A1) (5% = profit margin, B72 from pricing worksheet).

In sheet1, C2 should populate B77 (from the pricing worksheet), D2 should populate B79 (pricing worksheet) and E2 should populate B81 (pricing worksheet).

The columns in sheet1 are different than B77-B81 because I combined the OT rate and Holiday rate since they are equal to each other.

 

[awoohaw]

Okay, if you cannot update the row lables in 68,69,70, then the lookup formula will be subject to corruption if you delete or insert rows in that worksheet .
And I missed the Bill Rate mapping, thanks.
I don't see where you have Double Holiday mapped?
So, just to get is confirmed:

5%	PAY RATE	REGULAR BILL RATE	HOLIDAY / OT BILL RATE	DOUBLE TIME / HOLIDAY OT BILL RATE
B2 part of the lookup match to row 72.	C2 Bill Rate on Row 77	D2 Overtime Rate on Row 79	E2 Holiday Overtime Rate on Row B81

 

[lgarcia2509]

Okay, if you cannot update the row lables in 68,69,70, then the lookup formula will be subject to corruption if you delete or insert rows in that worksheet .
And I missed the Bill Rate mapping, thanks.
I don't see where you have Double Holiday mapped?
So, just to get is confirmed:

5%	PAY RATE	REGULAR BILL RATE	HOLIDAY / OT BILL RATE	DOUBLE TIME / HOLIDAY OT BILL RATE
B2 part of the lookup match to row 72.	C2 Bill Rate on Row 77	D2 Overtime Rate on Row 79	E2 Holiday Overtime Rate on Row B81

The pricing worksheet will never be changed, so that's okay.

Double holiday is combined in sheet1 D2. It includes holiday and regular OT rate. Since it's the same amount, I didn't create another column to separate them.

Everything looks correct on your example, but just confirming 5% is A1 and not B2.

 

[lgarcia2509]

The pricing worksheet will never be changed, so that's okay.

Double holiday is combined in sheet1 D2. It includes holiday and regular OT rate. Since it's the same amount, I didn't create another column to separate them.

Everything looks correct on your example, but just confirming 5% is A1 and not B2.

I'd like to be able to type in any pay rate in cell B2 and it populate the rest, if that's possible?

 

[awoohaw]

yes, the 5% is A1, my error. And in your Sheet1, there is no calcuation in column F, then?

 

[awoohaw]

yes, the 5% is A1, my error. And in your Sheet1, there is no calcuation in column F, then?

i think my eyes were crossed, my bad. i see now.

 

[lgarcia2509]

yes, the 5% is A1, my error. And in your Sheet1, there is no calcuation in column F, then?

Correct - it's blank.

 

[awoohaw]

okay, the Orange cells you input, the green cells are calculated. you can just use row 2, or use all rows to have a list (just drag formula down).

Book1
	A	B	C	D	E
1	0.05	PAY RATE	REGULAR BILL RATE	HOLIDAY / OT BILL RATE	DOUBLE TIME / HOLIDAY OT BILL RATE
2		$20.00	33.60	45.25	56.90
3		$21.00	34.84	47.51	59.75
4		$22.00	36.08	49.89	62.74
5		$23.00
Sheet1

Cell Formulas
Range	Formula
C2:C4	C2	=SUM((--($A$1='Pricing Worksheet'!$B$72:$J$72))*(--(B2='Pricing Worksheet'!$B$1:$J$1)*'Pricing Worksheet'!$B$77:$J$77))
D2:D4	D2	=SUM((--($A$1='Pricing Worksheet'!$B$72:$J$72))*(--(B2='Pricing Worksheet'!$B$1:$J$1)*'Pricing Worksheet'!$B$79:$J$79))
E2:E4	E2	=SUM((--($A$1='Pricing Worksheet'!$B$72:$J$72))*(--(B2='Pricing Worksheet'!$B$1:$J$1)*'Pricing Worksheet'!$B$81:$J$81))

here is the pricing worksheet i used:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Base Pay (Hourly)	20	21	22	20	21	22	20	21	22
2
72	Profit Margin	5%	5%	5%	6%	6%	6%	7%	7%	7%
73
77	Bill Rate	33.6	34.84	36.08	33.6	34.84	36.08	33.6	34.84	36.08
78
79	Overtime Rate	45.25	47.51	49.89	45.25	47.51	49.89	45.25	47.51	49.89
80	Holiday Rate	45.25	47.51	49.89	45.25	47.51	49.89	45.25	47.51	49.89
81	Holiday Overtime Rate	56.90	59.75	62.74	56.90	59.75	62.74	56.90	59.75	62.74
82
Pricing Worksheet

 

[awoohaw]

I had to mock up the last 6 columns of the rate sheet, so I may not have good values there, and it doesn't really test out 6% or 7 % profit margins. But, I think it works.

 

[awoohaw]

And if your pricing worksheet will not change in structure, you may want to consider using range names to make the formulas more understandable. If you want information on that just ask.