IF function

[excelRookie06]

Hello all... I've received great tips here in the past, so I'm back for more.

I'm trying to acheive the following... if, for example, cell A1 is a number, I wan't my resulting cell (let's call it B1)to show that number. If cell A1 is blank, I want B1 to show data that is linked to another cell, C1, . I've acomplished this using the following formula in cell B1:

=IF(ISNUMBER(A1),A1,C1)

MY PROBLEM is that I need to expand on this so that if cell C1 is blank, I want B1 to return a blank cell, rather than "zero" the way it is now.

Thanks for any help!

Jason

[mudraker]

=if(a1="","",if(isnumber(a1),a1,c1))

[excelRookie06]

great, thanks... that works

[excelRookie06]

Sorry, one follow up question. I failed to mention that if cell A1 is blank and it links to cell C1 and returns a value, I need that value to be different color font (green for my purposes). Any chance this is possible?

Thanks again.

[Paul]

First, how does mud's formula "work" for you? Your original request was that if there's a number in A1, return it to B1. If there's no number in A1, get the value from C1. If there's no value in C1 either, leave B1 blank.

Mud's formula, =if(a1="","",if(isnumber(a1),a1,c1)), checks to see if A1 is blank, and if so returns nothing to cell B. That's where it stops. It will never even check to see if there's a value in C1, which you want it to do, right?

For example, if A1 is blank, and C1 has the number 3 in it, that formula will leave B1 blank because A1 is blank (result from the TRUE portion of the first IF statement).

I'd suggest the following formula instead:
=IF(A1="",IF(C1="","",C1),A1)

This will check if A1 is blank, and if so, it will check if C1 is blank. If that is also true, B1 will be blank. If A1 is blank and C1 isn't, it will return C1. If A1 isn't blank, it will skip the second IF and simply return A1.

As for formatting the results coming from column C a different color, select column B and click Format->Conditional Formatting...

Change 'Cell value is' to 'Formula is', then type the following formula:
=B1=C1

Then click the Format button and change the Font color to green. Click OK twice to return to your sheet. Anytime the value in B equals the value in column C, it will be green.

[excelRookie06]

Thanks Paul, you are right that the original formula didn't quite work. I made an adjustment that somehow worked though. Thanks for the advice on the conditional formatting, I'll try that next