Formulas

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Okay...I measure for carpet and hardwood floor and have created a spreadsheet
to calculate the costs for me. The problem I am having is that there are
variables within a cell. For example, we charge either $4, $4.25, $4.50 or
$5.00 per yard per install. I would like to be able to put in a, b, c, or d
and have it calculate the cost associated with that letter. Also, there is
always a shipping charge but only sometimes a padding charge. I am on the
border of 2 states, so the tax changes based on that. Are there simple
formulas that can calculate these items?

Thanks!
 
=E1*(CHOOSE(CODE(A1)-96,4,4.25,4.5,5)*B1+C1+D1)

Where A1 contains the code for price by yard either a or b or c or d
Where B1 contains the number of yards
Where C1conains the shipping charge
Where D1 contains the paddding charge
 
If you put a, b, c or d in A1, then this formula will return the rate
per yard:

=CHOOSE(CODE(A1)-96,4,4.25,4.5,5)

As for your other queries, you can have simple codes like Y or N in a
column (eg E) for padding charges and a formula like:

=IF(E1="Y",chg,0)

where chg is your padding charge (or rate, to be multiplied by the
area), and a formula like:

=IF(G1="state_1",x,y)

where G contains the state and x is the tax rate for state_1 and y is
the tax rate for the other state.

Hope this helps.

Pete
 
Pete, Can I send this file to you for review? I've added comments for
better explanation... :)
--
Sharri Jinnah
Carpet Direct


Pete_UK said:
If you put a, b, c or d in A1, then this formula will return the rate
per yard:

=CHOOSE(CODE(A1)-96,4,4.25,4.5,5)

As for your other queries, you can have simple codes like Y or N in a
column (eg E) for padding charges and a formula like:

=IF(E1="Y",chg,0)

where chg is your padding charge (or rate, to be multiplied by the
area), and a formula like:

=IF(G1="state_1",x,y)

where G contains the state and x is the tax rate for state_1 and y is
the tax rate for the other state.

Hope this helps.

Pete
Click to expand...
 
Back
Top