If(Isnumber(Search Question

[Island Gryl]

I'm getting a message that my formula won't work because it is a circular reference and just wanted to check in with you experts to see if my approach can work or if I have to approach this differently. It seems like its a simple issue and I hate to have to add a column to deal with this.

I have a Quantity Column L16 that contains a 1 and a Item name column M16 that explains that the item sold is a "Buy 2 Get 1 Free"
The formula that I am using resides in L16 '=IF(ISNUMBER(SEARCH("2",M16)),(L16+2),L16)
It returns a 0 after explaining that the circular reference might make my results unreliable.
I was hoping for a 3.

I am most grateful for anyone who has time to comment on this.

Thank you

 

[bebo021999]

L16 has value "1" already, then you can not put a formula in L16 and expect new value = new value + 1 (It is possible only with VBA code)
It is OK if formula in other cell, i.e, N16:
=L16+IF(ISNUMBER(SEARCH("2",M16)),2,0)
or shorter:
=L16+ISNUMBER(SEARCH("2",M16))*2

 

[Island Gryl]

Hi Bebo:

Thankyou for the clarification, I think I knew that but overlooked it because this seems like it should be simple. Trying to make the impossible happen

They both work perfectly but I don't understand why the second one works.
I've never seen the notation *2
So it says 1+ IF True *2, so True must be seen as 1 by Excel?

 

[bebo021999]

So it says 1+ IF True *2, so True must be seen as 1 by Excel?

Absolutely yes.
True = 1
False = 0
1+ true = 2
1 + true*2 = 3