Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with IF function please 03.08.08
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with IF function please 03.08.08
I would suggest
=if(E17="V",G17*.0175,0) So that, if any value other than V, then $0. "Essjay" wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with IF function please 03.08.08
Oops.. that's .175, not .0175...
"Essjay" wrote: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with IF function please 03.08.08
Assuming E and V are the only entry possibilities for Column E, try this
formula... =G17*(E17="V")*17.5% Rick "Essjay" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with IF function please 03.08.08
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 "Essjay" wrote in message ... 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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with IF function please 03.08.08
Correction:
In F17 put this formula =IF(E17="V",G17*17.5%,IF(E17="E",0,"?")) Tyro "Tyro" wrote in message . net... In G17 put this formula =IF(E17="V",17*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 "Essjay" wrote in message ... 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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with IF function please 03.08.08
Try this in F17:
=(E17="V")*G17*17.5% -- Biff Microsoft Excel MVP "Essjay" wrote in message ... 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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with IF function please 03.08.08
Obfuscation? This person is a tyro. Simple IF's will help the OP
Tyro "T. Valko" wrote in message ... Try this in F17: =(E17="V")*G17*17.5% -- Biff Microsoft Excel MVP "Essjay" wrote in message ... 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. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with IF function please 03.08.08
Thanks for your prompt help! Mission accomplished!
"Sean Timmons" wrote: I would suggest =if(E17="V",G17*.0175,0) So that, if any value other than V, then $0. "Essjay" wrote: 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. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with IF function please 03.08.08
Thanks for prompt assistance. Done!
"Rick Rothstein (MVP - VB)" wrote: Assuming E and V are the only entry possibilities for Column E, try this formula... =G17*(E17="V")*17.5% Rick "Essjay" wrote in message ... 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. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with IF function please 03.08.08
Thanks for your prompt help. I was almost there... you've all been fantastic!
"Tyro" wrote: Correction: In F17 put this formula =IF(E17="V",G17*17.5%,IF(E17="E",0,"?")) Tyro "Tyro" wrote in message . net... In G17 put this formula =IF(E17="V",17*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 "Essjay" wrote in message ... 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. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with IF function please 03.08.08
What if the value is not V or E?
Tyro "Essjay" wrote in message ... Thanks for your prompt help! Mission accomplished! "Sean Timmons" wrote: I would suggest =if(E17="V",G17*.0175,0) So that, if any value other than V, then $0. "Essjay" wrote: 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. |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with IF function please 03.08.08
This person is a tyro.
If you say so. Is that good or bad? -- Biff Microsoft Excel MVP "Tyro" wrote in message et... Obfuscation? This person is a tyro. Simple IF's will help the OP Tyro "T. Valko" wrote in message ... Try this in F17: =(E17="V")*G17*17.5% -- Biff Microsoft Excel MVP "Essjay" wrote in message ... 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. |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with IF function please 03.08.08
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 "T. Valko" wrote in message ... This person is a tyro. If you say so. Is that good or bad? -- Biff Microsoft Excel MVP "Tyro" wrote in message et... Obfuscation? This person is a tyro. Simple IF's will help the OP Tyro "T. Valko" wrote in message ... Try this in F17: =(E17="V")*G17*17.5% -- Biff Microsoft Excel MVP "Essjay" wrote in message ... 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. |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with IF function please 03.08.08
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" wrote in message et... Obfuscation? This person is a tyro. Simple IF's will help the OP Tyro "T. Valko" wrote in message ... Try this in F17: =(E17="V")*G17*17.5% -- Biff Microsoft Excel MVP "Essjay" wrote in message ... 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. |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with IF function please 03.08.08
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 .. "Bob Phillips" wrote in message ... 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" wrote in message et... Obfuscation? This person is a tyro. Simple IF's will help the OP Tyro "T. Valko" wrote in message ... Try this in F17: =(E17="V")*G17*17.5% -- Biff Microsoft Excel MVP "Essjay" wrote in message ... 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. |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with IF function please 03.08.08
May I add, now I learned a more efficent way to perform the calculation, so I
can't complain too much. "Bob Phillips" wrote: 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" wrote in message et... Obfuscation? This person is a tyro. Simple IF's will help the OP Tyro "T. Valko" wrote in message ... Try this in F17: =(E17="V")*G17*17.5% -- Biff Microsoft Excel MVP "Essjay" wrote in message ... 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. |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with IF function please 03.08.08
I doubt it is more efficient. Obfuscated, certainly.
Tyro "Sean Timmons" wrote in message ... May I add, now I learned a more efficent way to perform the calculation, so I can't complain too much. "Bob Phillips" wrote: 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" wrote in message et... Obfuscation? This person is a tyro. Simple IF's will help the OP Tyro "T. Valko" wrote in message ... Try this in F17: =(E17="V")*G17*17.5% -- Biff Microsoft Excel MVP "Essjay" wrote in message ... 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. |
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with IF function please 03.08.08
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. -- Regards Roger Govier "Essjay" wrote in message ... 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. |
#20
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with IF function please 03.08.08
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) "Sean Timmons" wrote in message ... May I add, now I learned a more efficent way to perform the calculation, so I can't complain too much. "Bob Phillips" wrote: 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" wrote in message et... Obfuscation? This person is a tyro. Simple IF's will help the OP Tyro "T. Valko" wrote in message ... Try this in F17: =(E17="V")*G17*17.5% -- Biff Microsoft Excel MVP "Essjay" wrote in message ... 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. |
#21
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with IF function please 03.08.08
"Tyro" wrote in message . net... I doubt it is more efficient. Obfuscated, certainly. Well I am afraid you are just plain wrong there. I just timed it and Biff''s is 31% faster than Sean's first formula, and 41% faster than yours. By my reckoning that is more efficient. |
#22
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with IF function please 03.08.08
I don't think that execution time should take predence over the
understanding of a simple formula. How much time is involved here. 10 microseconds? 20 microseconds, 5 days? Tyro "Bob Phillips" wrote in message ... "Tyro" wrote in message . net... I doubt it is more efficient. Obfuscated, certainly. Well I am afraid you are just plain wrong there. I just timed it and Biff''s is 31% faster than Sean's first formula, and 41% faster than yours. By my reckoning that is more efficient. |
#23
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with IF function please 03.08.08
That is another argument. You said, and I quote, ... I doubt it is more
efficient. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Tyro" wrote in message ... I don't think that execution time should take predence over the understanding of a simple formula. How much time is involved here. 10 microseconds? 20 microseconds, 5 days? Tyro "Bob Phillips" wrote in message ... "Tyro" wrote in message . net... I doubt it is more efficient. Obfuscated, certainly. Well I am afraid you are just plain wrong there. I just timed it and Biff''s is 31% faster than Sean's first formula, and 41% faster than yours. By my reckoning that is more efficient. |
#24
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with IF function please 03.08.08
Hi Roger
Thanks for the comprehensive answer. You're correct in that my query does deal with UK VAT. The only reason I used the letters V & E was more as an "aide memoire" for the person doing the input ~ V to stand for VATable and E for Exempt. The VAT element will only ever fall into the customary standard 17.5% (unless otherwise amended by HMG!), or be Exempt, so I was trying to apply the KISS principle to the formula. I will re-jig the columnisation to reflect your suggestion. I'd just like to thank everyone for the time they've taken with my query and for sharing their knowledge so readily. Regards Essjay "Roger Govier" wrote: 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. -- Regards Roger Govier "Essjay" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
Nested IF Function, Date Comparing, and NetworkDays Function | Excel Worksheet Functions |