+ Reply to Thread
Results 1 to 3 of 3

Complicated nested formula assistance (believe I can use "if" formula, not 100% sure)

  1. #1
    Registered User
    Join Date
    03-27-2012
    Location
    Tampa, FL
    MS-Off Ver
    Excel for M365
    Posts
    53

    Exclamation Complicated nested formula assistance (believe I can use "if" formula, not 100% sure)

    Hi everyone,

    Please see my problem below (file attached):

    I believe I only need to write one formula and it can be applied to columns K-N. I was going to use customer number (as opposed to name) when I wrote the formula.

    Here are the parameters:
    - If invoice is over BRL 5,000.00 (column F) and customer is Americel, Claro, Nextel or Tim Cellular – then columns K-N should be billed amount (column F) multiplied by tax rate (amounts in row 3, columns K-N).
    o Any invoice amount under BRL 5,000 (column F) for the above customers – then column K should be billed amount (column F) multiplied by tax rate (in row 3, column K) and columns L-N should be zero.
    o However, if invoice tax amount is under BRL 10.00 (column G) for above customers – then columns K-N should be 0
    - Customer Vivo should be zero for columns K-N.

    Thanks in advance for your assistance,
    Jessica
    Attached Files Attached Files

  2. #2
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Complicated nested formula assistance (believe I can use "if" formula, not 100% sure)

    In Col K
    Please Login or Register  to view this content.
    Copy down

    In Col L
    Please Login or Register  to view this content.
    Copy dwon and across into Col M-N
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Complicated nested formula assistance (believe I can use "if" formula, not 100% sure)

    I used (in K4 dragged across and down)
    =IF(OR($B4={"AMERICEL S/A","Claro","Nextel","Tim Cellular"}), IF($F4>=5000, $F4*K$3, IF($F4< 10, 0, IF(K$2="IRRF WH Tax",$F4*K$3,0))),0)

    There are some customers you did not mention (i.e CTBC CELULAR S/A). Delete: Edit: I see Ace used Column C to sort customer type. Very clever.
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1