Aug 07 2021 04:31 AM
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?
Aug 07 2021 04:47 AM
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.
Aug 07 2021 05:40 AM
Aug 07 2021 05:52 AM - edited Aug 07 2021 06:01 AM
@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.
Aug 07 2021 06:06 AM - edited Aug 07 2021 06:22 AM
Aug 07 2021 06:27 AM
@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.
Aug 07 2021 04:47 AM
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.