Share via

Using IF(), AND(), and LEFT() together

Anonymous
2017-10-08T13:35:06+00:00

Hi, I am trying to combine the functions IF(), AND(), and LEFT() in a formula, however excel keeps saying #VALUE! or #NAME? instead of calculating the formula. This is the formula I typed, please help me find out what the problem with it is. Thank you in advance.

=IF(AND(LEFT(Product_Code,2)="CW",Stock_Quantity>15*Average_Daily_Sales),25%,"")

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

DaveM121 891K Reputation points Independent Advisor
2017-10-08T15:24:06+00:00

Is Product_Code a cell or a range of cells, same question for Stock_Quantity and Daily Sales . . . .

Without being able to see the sheet it is hard to visualize, maybe provide a screenshot . . .

Was this answer helpful?

1 person found this answer helpful.
0 comments

Answer accepted by question author

DaveM121 891K Reputation points Independent Advisor
2017-10-08T15:09:38+00:00

Yes your formula is correct, a blank cell is what you would expect to find with those values . . .

It was your references which needed adjusting!

Was this answer helpful?

1 person found this answer helpful.
0 comments

7 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. DaveM121 891K Reputation points Independent Advisor
    2017-10-08T14:34:33+00:00

    So with those values I just get a blank return i.e. ""

    That function with those values evaluates to: =IF(AND(FALSE,FALSE),25%,"")

    I don't get an error, are you sure these references are correct? Product_Code Stock_Quantity Average_Daily_Sales

    Was this answer helpful?

    0 comments
  2. Anonymous
    2017-10-08T14:23:41+00:00

    Sure, for Product_Code the value is CM_032, Stock_Quantity is 400, and lastly Average_Daily_Sales is 255.

    Was this answer helpful?

    0 comments
  3. DaveM121 891K Reputation points Independent Advisor
    2017-10-08T13:47:50+00:00

    There is nothing wrong with the syntax of your function . . .

    The answer must lie in the data in the cells referenced, can you provide the worksheet with sample data or just the values in the referenced cells . . .

    e.g Product_Code, Stock_Quantity . . etc.

    Was this answer helpful?

    0 comments