Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Help with Formula please (Excel 2002/2003)

    Hi

    I am having a problem with the formula below. as it is the formula works. But I need to add to it, I only want it to return Shipthrough is Too Low if Match is "No and a cell Named NetSales is below 2000




    =IF(COUNTIF('Lead Range'!R7:R96,"<>")=0,"No Data",IF(ISNA(MATCH("No",'Lead Range'!BI7:BI96,0)),"Shipthrougth OK","Shipthrough is Too Low"))


    Regards

    Braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Help with Formula please (Excel 2002/2003)

    =IF(COUNTIF('Lead Range'!R7:R96,"<>")=0,"No Data",IF(ISNA(MATCH("No",'Lead Range'!BI7:BI96,0)),"Shipthrougth OK",if(netsales<2000,"Shipthrough is Too Low","something else")))
    I guess.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Help with Formula please (Excel 2002/2003)

    <P ID="edit" class=small>(Edited by macropod on 12-Mar-07 20:40. Argument sign switch for NetSales>=2000)</P>Hi Braddy,

    Try:
    =IF(COUNTIF('Lead Range'!R7:R96,"<>")=0,"No Data",IF(OR(ISNA(MATCH("No",'Lead Range'!BI7:BI96,0)),NetSales>=2000),"Shipthrougth OK","Shipthrough is Too Low"))
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Help with Formula please (Excel 2002/2003)

    Hi Guys

    Were not quite there yet. I need to say if netsales is <2000 and Match = "No" then netsales is Too Low else , OK

    Regards

    Braddy
    If you are a fool at forty, you will always be a fool

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Help with Formula please (Excel 2002/2003)

    Did you try macropod's revised formula - it does exactly that.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Help with Formula please (Excel 2002/2003)

    Hi Rory

    Yes I'm sorry I made an error with it.

    Thanks to all who replied.

    Braddy
    If you are a fool at forty, you will always be a fool

Posting Permissions

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