Forum Discussion

Copper Contributor

Aug 07, 2021

Solved

Help with a formula

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 vali...

Test.xlsx11 KB

Formulas and Functions

Riny_van_Eekelen
Aug 07, 2021
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.
Test-4.xlsx10 KB

Riny_van_Eekelen

Platinum Contributor

Aug 07, 2021

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.

Test-4.xlsx10 KB

djshaw63

Copper Contributor

Aug 07, 2021

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.

Riny_van_Eekelen
Platinum Contributor
Aug 07, 2021
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.
- djshaw63
  Copper Contributor
  Aug 07, 2021
  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!!
  - Riny_van_Eekelen
    Platinum Contributor
    Aug 07, 2021
    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.