I have a spreadsheet where I use the formula IF(BA21,”x”,”£0.00″). Basically, if the checkbox in BA21 is ticked, I get an x in the cell, otherwise I want to get a currency value of £0.00. I want to be able to replace that currency manually with e.g. £5.20 if I need to.
The problem here is I’m dealing with two different types of format in the same cell – text and currency. When I enter the currency value e.g. £5.20, the formatting then defaults to numeric then I get £5.
I’ve racked my brains to find another way to do this, but so far, nothing!
Do you have any suggestions as to how I can make this work in a tidier manner?
I run a lottery syndicate and this spreadsheet is a record of members contributions and weekly winnings. I have a “Week in play” field with a tick box. If that is ticked, then subsequent weeks show as blank by using the x or £0.00 value. If the tick box is unticked, what’s won that week is entered instead of the formula. It looks tidy, but the underlying code is messy.
—–
Jock Wilson
Just use a custom format for the cell. So the formula would be:
IF(BA21,-1,5.2)
Then select the cell and go to the Format sidebar, and the Cell tab at the top. Select the Data Format "Create Custom Format..."
See https://macmost.com/learning-to-use-cell-formatting-in-mac-numbers.html for a lesson on how to use custom formats.
The format you want is the #,### followed by the .##
Then click on the .## and turn on Show Trailing Zeros.
Then add £ at the beginning so the result is £ #,### .00
Then add a rule. Set that to "if equal to" -1 then the format is just "x."
Now when the checkbox is checked, the value is -1 and you get an "x" in the cell. Otherwise, you get values shown as £5.20.
Thank you very much - this does exactly what I needed - I've been looking for a solution to this for at least two years now! Absolutely superb service!