Help with IF function please 03.08.08

[Essjay]

Can anyone help with syntax for an IF formula as under:
If E17=E (for exempt) then F17=$0.00 but
If E17=V (for VAT) then F17=G17*17.5%
I have an invoice on which some items are taxable and some not. If I put
the E or V letter in Column E, then I want it to return either a zero value
or to calculate the tax in column F on Column G at 17.5%. Is this possible?

e.g. A B - D E F G
Qty. Desc. E $0.00 200.00
Qty. Desc. V $147.00 840.00

Any help much appreciated.

 

[Sean Timmons]

I would suggest
=if(E17="V",G17*.0175,0)

So that, if any value other than V, then $0.

 

[Sean Timmons]

Oops.. that's .175, not .0175...

 

[Rick Rothstein \(MVP - VB\)]

Assuming E and V are the only entry possibilities for Column E, try this
formula...

=G17*(E17="V")*17.5%

Rick

 

[Tyro]

In G17 put this formula =IF(E17="V",F17*17.5%,IF(E17="E",0,"?"))
You don't state what to put in G17 if E17 is not a V or an E so I put a ?

Tyro

 

[Tyro]

Correction:

In F17 put this formula =IF(E17="V",G17*17.5%,IF(E17="E",0,"?"))

Tyro

 

[T. Valko]

Try this in F17:

=(E17="V")*G17*17.5%

 

[Tyro]

Obfuscation? This person is a tyro. Simple IF's will help the OP

Tyro

 

[Essjay]

Thanks for your prompt help! Mission accomplished!

 

[Essjay]

Thanks for prompt assistance. Done!

Assuming E and V are the only entry possibilities for Column E, try this
formula...

=G17*(E17="V")*17.5%

Rick

 

[Essjay]

Thanks for your prompt help. I was almost there... you've all been fantastic!

 

[Tyro]

What if the value is not V or E?

Tyro

Thanks for your prompt help! Mission accomplished!

 

[T. Valko]

This person is a tyro.

If you say so. Is that good or bad?

 

[Tyro]

It both. It's bad if you're a tyro. If's ok if you've been programming for
44 years as I have. I've seen obfuscation that would blow your socks off
<g>.

Tyro

 

[Bob Phillips]

If the OP doesn't understand, he can always ask how it works and why Biff is
suggesting it that way. It's called improving your skills.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Tyro]

I think you missed the point. The OP is just beginning to learn. It's
counter productive to use advanced techniques just to save a few keystrokes
or confuse the OP with something he/she doesn't grasp at all. (A snow job).
You have to see it from the beginner's point of view and apply the KISS
principle. With time and experience, the OP will learn more sophisticated
techniques. Let's not jump to advanced calculus before we have learned
simple arithmetic. BTW, I blew the final calculus exam in college. I got
99%. I screwed up royally by not putting +C at the end of an integration
answer. Lost 1%. Cost me a perfect paper.

Tyro

..

 

[Sean Timmons]

May I add, now I learned a more efficent way to perform the calculation, so I
can't complain too much.

 

[Tyro]

I doubt it is more efficient. Obfuscated, certainly.

Tyro

 

[Roger Govier]

Hi

You have received many answers, each of which will deal with the calculation
you asked.
However, if you are dealing with UK VAT, I have some observations to add.

Under UK HMCE rules there are codes for several different VAT rates -
Z,X,E,O,S,L,H,I,P
There is no VAT code V, it should be S for Standard to attract a current
rate of 17.5% , so from both your viewpoint, and that of the invoice
recipient, it would serve you well to comply with the HMCE directive.

L - Lower currently 5% (only applies the Domestic Fuel bills and is
probably something you would not be using)
H - Higher rate is not currently being used
S - Standard is currently 17.5%
P - Partial Exemption (unlikely to be used in your particular case - applies
to some property situations)

X - Excluded, E - Exempt, O - Outside the scope and Z - Zero are all 0%

Personally, I would have Column F as the Net Amount, Column G as the VAT
Amount and column H as the Gross amount.
The formula in column G would then become
=IF(E17="S",ROUND(F17*17.5%,2),0)

Without knowing your circumstances, I would suspect that it is is S and Z
you should be using, rather than V and E.
You should confirm with your HMCE inspector to ensure that you are
complying.

 

[Bob Phillips]

And that is another benefit, others read these posts and learn things too.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)