• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help With String Of Numbers Using IF

E

edisrael

New Member
We're trying to create a formula using the IF function and can't seem to get it right.

What we're trying to accomplish is if the product is 0(zero), then enter 0.00; if the product is between 1-175, then enter 175.00; if over 175, then enter the actual product.

This is what we're attempting to use:

=IF(PRODUCT(B13:C13)<175,"175.00",IF(PRODUCT(B13:C13)=0,"0.00",PRODUCT(B13:C13)))

The problem is that if the product is 0(zero), then the answer is still shows as 175.00

Any help?
 
Hi,


Does this work for you


=IF(PRODUCT(B13:C13)=0,0,IF(AND(PRODUCT(B13:C13)>0,PRODUCT(B13:C13)<175),175,PRODUCT(B13:C13)))


Format the cell to number with two decimal places
 
Hi, edisrael!

I think you've misplaced the chainded conditions in your formula, so it's precedence doesn't reflect what you want.

It'll always displays "175.00" for 0 (zero), because 0 is less tan 175. And also avoid using "175.00" and "0.00" (string value) instead of 175 or 0 (number value) with the cell properly formated as number with two decimal places; it's safer for further calculations involving this cell.

Try this:

=SI(O(PRODUCTO(B13:C13)=0;PRODUCTO(B13:C13)>=175);PRODUCTO(B13:C13);175) ---> in english: =IF(OR(PRODUCT(B13:C13)=0,PRODUCT(B13:C13)>=175),PRODUCT(B13:C13),175)

Hope it'll work fine.

Regards!
 
Try:

=IF(B13*C13=0,0,IF(B13*C13<175,175,B13*C13))


Note: There is no need for the Product function with only 2 numbers
 
You must log in or register to reply here.
Back
Top