[Solved] Formula referencing formula

[jboush]

A1 and B1 contain formulae pulling numbers from elsewhere. C1 contains a formula: =if(A1>B1;A1;B1) and never pulls a number from either A1 or B1. What am I doing wrong?

[FJCC]

It is hard to say without a file to look at. What does appear in C1?
Can you upload a file. To do that, click PostReply and look for the Upload Attachment tab just below the box where you type a response.

[jboush]

nothing appears

[FJCC]

The formula in C1 is behaving as expected. It is

Code: Select all

=IF(A1>B1;A1;B1)

A1 contains an empty string, "", because of its formula and B1 contains 2. If you test the condition of the IF function by writing

Code: Select all

= A1 > B1

in D1, you will see that it is TRUE. Therefore, the IF in C1 returns the content of A1, which is an empty string and the cell looks blank.

[jboush]

so you're saying that the formula reads nothingness as greater than a numerical value

is there no way to alleviate the clutter that replacing "" with a bunch of zeros will cause (in the long run, that is. the sheet will be filled with these)

[FJCC]

You are comparing a number to a character, so the sorting order is not what you expect. A formula cannot return nothing. The formula in A1 returns an empty string, which, though empty, is still a string. When sorting, numbers are smaller than any string, even an empty one. Try sorting column A in the attached file. Note that A3 and A7 contain numbers while all of the other cells in A2:A7 contain text with A5 containing the empty string and A6 containing the string "2". Sorting in ascending order places the numbers first, then the empty string, then the text 2, then A, then E.

[jboush]

ok, thank you

[karolus]

why not simply =MAX(A1:B1)

[jboush]

that also works, ty