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]

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.

I just want to say that I appreciate your help SO MUCH! I myself was going cross-eyed yesterday and logged off.

In trying the formulas just now, it does pull the correct data, however, how can we make it so I can select any percentage (5-15%) in the drop-down and it still give us the correct data?

Say I just wanted the rate card to be 2 rows, and wanted to type in any pay rate in B2 with any percentage in A1 - is there a formula for that? Maybe Switch() and IF()?

 

[awoohaw]

I'm not sure what you're asking. when you put in the % and the pa yrate, the formulas in C, D, & E return values.
So, if you want to put in a different % from the drop or pay rate, they need to be in your pricing worksheet.

Otherwise you need a new calculation (although it would be similar). What are the elements (criteria to select, and variable to use the in the calculation)? And wha tis the calculation?

 

[lgarcia2509]

I'm not sure what you're asking. when you put in the % and the pa yrate, the formulas in C, D, & E return values.
So, if you want to put in a different % from the drop or pay rate, they need to be in your pricing worksheet.

Otherwise you need a new calculation (although it would be similar). What are the elements (criteria to select, and variable to use the in the calculation)? And wha tis the calculation?

The %s in the drop-down are included in the pricing worksheet, only the rows are hidden.

In the pricing worksheet, the elements are the profit margin (%), bill rate, OT rate, and holiday OT.

Something I noticed in the rate card -- when I input 5% at $20 and $21, they both return the correct values. Anything further than that, it shows $0.

I hope this answers your questions.

 

[awoohaw]

if you are using my exact formula you will stop at a certain column you need to adjust the formulas to fit your worksheets.

How is the question you posted in #21 different from the solution I had given? What is different? Is it just the % and dollar amount? if so, you need to only update your formula to expand the columns. Otherwise, you need to give me the new changes in the calculation.