Help with a formula

[djshaw63]

I have a spreadsheet that calculates the total price of various optional items associated with building a new home. For each item on the very long list I have a cell that acts as a checkbox. The valid entries in the check box are "n" (I don't want it), "?" (I need more information before deciding), "y" if I do want 1 of the associated item or a numeric entry greater than 1 if I want more than 1 of a particular item. The formula in cell F2 in the attached sheet for extending the cost for an item in row 2 that I want is as follows: =IF(OR(B2="y",B2>0),IF(B2="y",D2,D2*B2),""). It properly calculates the extended price for an item if I enter either "y" or a specific quantity greater than 1 in cell B2. But if I enter anything else, e.g. a "n", in the checkbox, I get a #VALUE! error; I want the cell to be blank. I can get rid of that error by modifying the formula as I have done in cell F3 with IFERROR, like this: =IFERROR(IF(OR(B3="y",B3>0),IF(B3="y",D3,D3*B3),""),"") as shown in cell F3. But I'd really like to know what is wrong with my original version. Any ideas folks?

[#2 · Accepted solution]

@djshaw63 I would like to suggest that you change the set-up slightly. Rather than having a cell to be "y" or a number, why not have one column for y, n or ?  and one where you fill in the qty.

Then use a formula that checks tests column B for "y" (True=1, False=0) X qyt X Unit cost.

See attached.

[#3]

Thanks! I like that idea. I also considered not using a "y" at all but just a numeric value > 0 if I wanted that option at all. But as a very long time Excel user (since Multiplan on the Mac in '84), I am just very frustrated that I can't figure out why my present formula generates the #VALUE! error.

[#4]

@djshaw63 Well, that's because "n" is greater than zero. So, even when you have "n" in column B, the first argument will return TRUE. Than the next argument is evaluated IF(B3="y",D3,D3*B3) . Since B3 does not contain "y", Excel executes the calculation D3*B3 which returns the #VALUE error as you can't multiply a number by a text.

[#5]

• Ah HA!! That surely explains it. Never knew that a text value is evaluated as greater than 0. So I will use your suggested additional cell with a numeric value that will be invoked if the checkbox contains a "y". MANY THANKS Riny!!

[#6]

@djshaw63 You could use it this way:

=IF(OR(B3="y",ISNUMBER(B3)),IF(B3="y",D3,D3*B3),"")

Assuming that you will never enter a negative quantity. 

[#7]

I like that too!