Forum Discussion
Help with a formula
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.
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.
- 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.
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.
- 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!!
