+ Reply to Thread
Results 1 to 11 of 11

VAT Formula for SAGE: IF cell a1 = T1, and cell a2 has a value of 120.00, then a3 =a2/100*

  1. 03-07-2013, 06:34 AM #1
    MRT0PP5
    MRT0PP5 is offline
    Registered User
    Join Date
    03-07-2013
    Location
    Yorkshire
    MS-Off Ver
    Excel 2003
    Posts
    6

    VAT Formula for SAGE: IF cell a1 = T1, and cell a2 has a value of 120.00, then a3 =a2/100*

    VAT Formula for SAGE: IF cell A1 contains the text T1, and cell A2 has a value of 100.00, then A3 =A2/100*20

    also if A1 contains the text T9, and cell A2 has a value, then A3=0 zero.

    Hello All, I'm new, I have tried searching but can not find anything.

    I need a formula for when I'm entering sales invoices onto a spreadsheet that the correct VAT element is displayed at the moment I'm having to type them in and wondered if there was a quick fix to my problem. Please help if you can.

    Kind regards

    MrT0PP5
    Reply With QuoteRegister To Reply
  2. 03-07-2013, 06:53 AM #2
    Kevin UK
    Kevin UK is offline
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    Re: VAT Formula for SAGE: IF cell a1 = T1, and cell a2 has a value of 120.00, then a3 =a2/

    Hi MRT0PP5

    In A3, try the following:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Regards Kevin


    Merged Cells (They are the work of the devil!!!)
    Reply With QuoteRegister To Reply
  3. 03-07-2013, 06:57 AM #3
    MRT0PP5
    MRT0PP5 is offline
    Registered User
    Join Date
    03-07-2013
    Location
    Yorkshire
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: VAT Formula for SAGE: IF cell a1 = T1, and cell a2 has a value of 120.00, then a3 =a2/

    Hi kevin sorry but it just displays a zero?
    Reply With QuoteRegister To Reply
  4. 03-07-2013, 07:06 AM #4
    Kevin UK
    Kevin UK is offline
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    Re: VAT Formula for SAGE: IF cell a1 = T1, and cell a2 has a value of 120.00, then a3 =a2/

    Hi MRT0PP5

    See the attached, click the data validation arrow in A1.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 03-07-2013, 07:19 AM #5
    MRT0PP5
    MRT0PP5 is offline
    Registered User
    Join Date
    03-07-2013
    Location
    Yorkshire
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: VAT Formula for SAGE: IF cell a1 = T1, and cell a2 has a value of 120.00, then a3 =a2/

    Hi kevin, yeah i can see it works but not in my spreadsheet, how do i attach a file so i can show you it?
    Reply With QuoteRegister To Reply
  6. 03-07-2013, 07:26 AM #6
    Kevin UK
    Kevin UK is offline
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    Re: VAT Formula for SAGE: IF cell a1 = T1, and cell a2 has a value of 120.00, then a3 =a2/

    Go advanced and click the paperclip.

    Attach file.PNG
    Reply With QuoteRegister To Reply
  7. 03-07-2013, 07:39 AM #7
    MRT0PP5
    MRT0PP5 is offline
    Registered User
    Join Date
    03-07-2013
    Location
    Yorkshire
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: VAT Formula for SAGE: IF cell a1 = T1, and cell a2 has a value of 120.00, then a3 =a2/

    Here you go, please see attached.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 03-07-2013, 07:47 AM #8
    Kevin UK
    Kevin UK is offline
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    Re: VAT Formula for SAGE: IF cell a1 = T1, and cell a2 has a value of 120.00, then a3 =a2/

    Hi MRT0PP5

    See the attached, in J2 and copy down: =IF(I2="T1",H2*0.2,"")
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 03-07-2013, 08:03 AM #9
    MRT0PP5
    MRT0PP5 is offline
    Registered User
    Join Date
    03-07-2013
    Location
    Yorkshire
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: VAT Formula for SAGE: IF cell a1 = T1, and cell a2 has a value of 120.00, then a3 =a2/

    magnifico - problem solved...thank you Kevin you are a gem.

    kind regards

    Mrt0pp5
    Reply With QuoteRegister To Reply
  10. 03-07-2013, 08:06 AM #10
    MRT0PP5
    MRT0PP5 is offline
    Registered User
    Join Date
    03-07-2013
    Location
    Yorkshire
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: VAT Formula for SAGE: IF cell a1 = T1, and cell a2 has a value of 120.00, then a3 =a2/

    Hi Kevin, just one more thing if I enter "T9" can I make the text be Red in colour?
    Reply With QuoteRegister To Reply
  11. 03-07-2013, 08:23 AM #11
    Kevin UK
    Kevin UK is offline
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    Re: VAT Formula for SAGE: IF cell a1 = T1, and cell a2 has a value of 120.00, then a3 =a2/

    Hi MRT0PP5

    Yes, select column I > Format tab > Condioning formatting > Click the arrow under Condition 1 and select "Formula is" > In the next box enter: =I1="T9" > Format > Apply your formatting you require > OK
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1