Excel 2003 using a letter to define whether to multiply

  • Thread starter Thread starter Patrick
  • Start date Start date
P

Patrick

Ok, let me explain subject line. I have a spread sheet where I want to be
able to put in T in a cell which represents if tax is paid, what I would
like is that by having a t in one cell the cell with the formula for tax is
used. i.e. if cell B1 has a T in it, then cell J1 will run the tax cell for
B1 and add the tax.
Is it possible to use letters in this way? Currently I am using a 1 for tax
and 0 for no tax which does work.
Any input welcome.
Patrick
 
Hi

You'll have to use a function like EXACT, to differentiate between the upper
and lower case:
=IF(EXACT(B1,"T"),"Yes","No")
 
Say you have the net amount A1, you have your T or no T in B1, and th
gross amount in C1.

So to add 22% tax if there's a T in B1, you would put in C1,

=if(B1="T", A1+(A1*0.22), A1)

It looks to see if there's a T and if there is it multiplies the ne
amount by the tax decimal (in this case 22% = 0.22) and adds it to th
net amount, otherwise it just copies what's in A1.

I hope this helps, but could I also suggest that if there are se
criteria which define where and when to apply tax, that you shoul
forget about the T and write it into the formula.

For example, if every amount over 5000 has to have tax added, you coul
have the formula:

=if(A1>5000, A1+(A1*0.22), A1)

Or something similar depending on your needs
 
Ok, cool got that part that it recognises that the T is in that cell, my
question then would be how do I get it to use the formual for the tax which
is in another cell on the same row?
So far I have entered =IF(EXACT(E2,"T"),"Yes","No") , the cell with the
tax calculation is F2
Tax calculation cell is a simple =(C2*8.25) , C2 has the total for the cost
of product.
Thanks for the great help
Patrick
 
If A1 had the sale total,
And the tax rate was 8.25%,
Try something like this in J1:

=MAX(ROUND((B1="t")*A1*1.0825,2),A1)

With *no* "t" in B1, the sale total is returned "untaxed".
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Ok, let me explain subject line. I have a spread sheet where I want to be
able to put in T in a cell which represents if tax is paid, what I would
like is that by having a t in one cell the cell with the formula for tax is
used. i.e. if cell B1 has a T in it, then cell J1 will run the tax cell for
B1 and add the tax.
Is it possible to use letters in this way? Currently I am using a 1 for tax
and 0 for no tax which does work.
Any input welcome.
Patrick
 
Excellent, that is exactly what I needed, worked great
Thanks so much for the help
Patrick
 
Hi

Do you mean something like:
=IF(EXACT(E2,"T"),C2*8.25,0)
This performs the calculation C2*8.25 if E2 = T, otherwise it will display 0
 
Then what are you using J1 for?
You can *include* the test for the "t" within the tax calculating formula!

Just enter my formula into F2, and replace A1 with C2:

=MAX(ROUND((B1="t")*C2*1.0825,2),C2)
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Ok, cool got that part that it recognises that the T is in that cell, my
question then would be how do I get it to use the formual for the tax which
is in another cell on the same row?
So far I have entered =IF(EXACT(E2,"T"),"Yes","No") , the cell with the
tax calculation is F2
Tax calculation cell is a simple =(C2*8.25) , C2 has the total for the cost
of product.
Thanks for the great help
Patrick
 
=MAX(ROUND((B1="t")*A1*1.0825,2),A1)
With *no* "t" in B1, the sale total is returned "untaxed".
Click to expand...

Cool that also works and is a little tidier, Has been years since I have
used excel and it was just a little harder and more in depth to do these
sort of calculations before.
Thanks for the great advice
Patrick
 
Back
Top