Benches Uncategorized

3

Blank cell if no result

apple Verified

Imported from the Apple Support Forums via the Wayback Machine 🔗

Hello, I have Col B where a number value is going to be entered, the problem is with col J. Col J is the percentage of the number in Col B, if It is Blank I want the col J not to show an 0% right now if it is blank it shows a 0%, but once col B has a value is gives the correct answer. Sorry a relative easy question but I can't get this right. Can anyone help?

I would like col J to be blank until col B has a Value

The formula so far in Col J is "PAX OUT Flight 1÷12"

Thank you all

3 replies

Sign in or join Beachball Help to reply.

  1. 0

    Barry

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    Hi NN,

    Formulas here usually use cell coordinates by column and row, rather that using the column and row labels. For diagnostics, this makes the formulas fit the general case instead of being specific to the labels in a particular document.

        Your current formula: B2/12

    Try this revised version: IF(LEN(B2)<1,"",B2/12)

    The part in bold is your original. The rest is a 'switch' that prevents the calculation from occurring, and returns a null string to the cell IF no data has been entered in B2. As soon as there is data in B2, the LENgth of that data becomes at least 1 character, the comparison in IF returns FALSE, the calculation is done, and the result appears in the cell containing the formula.

    Regards,

    Barry

  2. 0

    NumberNovice

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    Thank you all for taking the time to read my last question but may I continue on (Thanks Barry)

    The below is another problem, I have this sheet and the problem is extracting the values from column P on this sheet.

    I am getting the below error messages in COL N I would like to get rid of the error for blank cells on previous page...

    I know this is the right place to come and ask.

    Thanks in advance.

    Rich

  3. 0

    Barry

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    Hi Rich,

    What is the "error message"? I see several error triangles, but no error messages, which can be revealed by clicking the red error triangle (and can be copied, then pasted here).

    Reading your formula, I suspect these are flagging a Syntax error.

    Have you looked in the Function Browser to read the syntax and description of the VLOOKUP function?

    Your VLOOKUP formula, in N5 of the second table:

    Syntax:            VLOOKUP(search-for, columns-range,        return-column, exact-match)

    Your formula: VLOOKUP($A5,           Flights::$A$1:$R$90,     COLUMN,      exact-match)

    Revisions:                                          Flights::$A:$R,                16,

    Reasons:

    search-for: This part is correct. You want VLOOKUP to get the value from column A of 'this row' on 'this table'

    columns-range: The important part here is the columns. If your Flights data is consistent in its use of the labelled columns, even beyond row 90, then specifying only the columns (A to R) is cleaner, and works just as well as specifying the column and row for each limit.

    column: Your formula does not indicate which column is to provide the data to be returned.

    VLOOKUP always searched in the leftmost column of the specified columns of the lookup table—in your case, column A of Flights.

    The column argument tells VLOOKUP which column contains the data to be returned, specifying that column by number, counting from the left, with the search column (A) set to 1. P is the sixteenth letter of the English alphabet, so this argument has to be set to 16.

    close-match: VLOOKUP can be set to accept a close-match or to demand an exact-match to the search value. As you want to collect information for a specific flight, exact-match is the correct choice. If the flight sought is not listed (or contains a spelling error or typo), VLOOKUP will throw a "can't find" error.

    Try the two changes suggested. The formula, as displayed should be entered in N5, and may be filled up or down from that row.

    Regards,

    Barry

    Although this question applies to the same document as your earlier one, it is about a different topic/issue. Future questions, whose topics differ, should be posted as a new question.

    Regards,

    Barry