Hi there. I am wanting a formula to fill in a cell if column 'A' has text in it.
For example all cells under black should be empty. Is there a formula that can do this?
Thanks in advance.
[Re-Titled by Moderator]
Original Title: Numbers formula
iMac (M3, 2023)
Hi Hayley,
Sorry, ≠ means 'not equal to'. Create it by typing <>
I should have said:
Type this in B3
IF(AND($A3<>"",B$2<>"),B$2*5,"")
Numbers changes <> to ≠ and * to × (which looks like a small x but it isn't. It is the multiplication operator).
And yes, Camelot's formula is easier to understand.
Regards,
Ian.
Hi Hayley,
Sorry, ≠ means 'not equal to'. Create it by typing <>
I should have said:
Type this in B3
IF(AND($A3<>"",B$2<>"),B$2*5,"")
Numbers changes <> to ≠ and * to × (which looks like a small x but it isn't. It is the multiplication operator).
And yes, Camelot's formula is easier to understand.
Regards,
Ian.
> Sorry, ≠ means 'not equal to'. Create it by typing <>
You can also type ≠ with option-=, which kind of makes sense when you think about it.
Option-= will work in any app, whereas substituting <> for ≠ is unique to Numbers.
YellowBox shows one way.
I thought of it a different way.
Specifically, B3:
=IF(ISBLANK($A3),"",5×B2)
This specifically checks if the cell in column A is blank. If it is, it returns an empty cell, otherwise it returns a value (in this case, 5 x the Ratio cell B2).
It's a little easier to read, but has the side effect that it will return 0 if there's no ratio value (not sure if that's an issue or not). You can always use Number formatting to suppress 0-values.
NOT is the clearest, at the minimal cost of a net four additional keystrokes.
In a similar vein, the Numbers formula editor auto-replaces / with ÷.
I've discovered it will also happily accept ÷ (which can be input by option-/).
I tried option-/ in Excel. It accepts it with no error! But it auto-converts the ÷ to /.
But using option-/ in Google Sheets pops up a keyboard shortcuts menu.
Since I'm lazy and easily confused I think I'm going to stick with typing <> for inequality and / for division. 😀
I'm guessing these have been used in spreadsheet software since the early days of more primitive keyboards.
SG
Hi Hayley,
Is this what you want?
Formula in B3 is IF(AND($A3≠"",B$2≠""),B$2×5,"")
Please call back with questions.
Regards,
Ian.
Thank you Camelot.
Thank you so much Yellowbox. I appreciate your expertise. Being in the fashion industry I struggled with the equal sign with the line through it so I took the other option that I could understand. You are very cleaver....thank you.
Camelot wrote:
Option-= will work in any app, whereas substituting <> for ≠ is unique to Numbers.
Thanks for the tip on Option-=! Not sure when it's good to use it, though.
Excel on my machine throws an error when I insert ≠ in a formula using Option-=
Excel seems to need <> in formulas.
As does Google Sheets.
Is seems it's not that "substituting <> for ≠ is unique to Numbers"
Instead it's "substituting ≠ for <> is unique to Numbers"
Or something like that!
In any case, with spreadsheets it's hard to go wrong inputing <> in formulas to mean inequality.
SG
> Excel on my machine throws an error when I insert ≠ in a formula using Option-=
I meant that typing option-= acts at the system level and will always input the ≠ character, not that it's universally understood/interpreted by every application.
> In any case, with spreadsheets it's hard to go wrong inputing <> in formulas to mean inequality.
True, except that Numbers only uses ≠ since it auto-replaces the <>. If you copy this formula and paste it into Excel it copies as ≠ and Excel doesn't know what to do about it. <sigh>.
Maybe the better option is to forego <> and ≠ completely:
=NOT(A1=B1)
:)
Perfect. Thank you so much. Worked a treat.
Thanks so much for the explanation Ian. Most appreciated. I will remember that for next time.
Hi SG. Fantastic! Thanks for the explanation, your time is much appreciated.
How to create a formula in Numbers to fill cells based on text in another column?
