How to create a formula in Numbers to fill cells based on text in another column?

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.

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.

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.

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)

:)

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

Thank you Camelot.