Next Excel Spreadsheet Question ... Leaving A Cell Blank

[joggerjayne]

If I put any number at all into Cell B7,

I want Cell H7 to show the word "Purchase"

If I leave B7 empty,

I want H7 to stay empty.

So far, in H7, I've got ...

=IF(B7>0,"Purchase", ,)

Not sure how to finish it off.

[jim360]

Just a quick test coming up...

bla bla >bla bla

[Old_Geezer]

=IF(B7>0,"Purchase","")

[Old_Geezer]

Actually that not quite right. You want it to not be empty, not >0. I'd have to look that up but I'm on lunch.

[jim360]

I think it's more sensible to test the value of cell B7 by comparing it specifically to an empty cell rather than greater than 0 (just in case, magically, you wanted to put a negative number into cell B7), but otherwise OG's solution is correct. To ask Excel to leave the cell empty, one should put in the final part two adjacent speech marks "".

Hence

=IF(B7="","","Purchase")

is my choice.

[joggerjayne]

Having anything nice, OG?

[Stamford_Richard]

=IF(B3>0,"Purchase","")

[Old_Geezer]

http://www.techonthenet.com/excel/formulas/isblank.php

TRy

IF ((ISBLANK(CELLNAME)),"","Purchase")

See if that works.

[Old_Geezer]

Soup & sunbites

[New Judge]

Try this:
in H7 type

IF(ISNUMBER(B7),"Purchase","")

This will return the word "Purchase" in H7 if B7 contains any number (positive, negative or zero) but leave it blank if cell H7 is blank or contains anything other than a number.

[New Judge]

Sorry Jane, you need "=" before the formula.

[joggerjayne]

Soup. Me too. Not sure what sun bites are.

[joggerjayne]

Wow, thanks guys. These all seem to work.

[jim360]

The catch with either of our formulas (although OG's is better syntax) is that if you accidentally put anything at all into cell B7 then it would come out as "Purchase". The only way around that might be to redesign the spreadsheet so that a "no purchase" cell in B7 is not left blank but filled with something like "null", over which there can be less ambiguity, and you could even have a double-if statement, such as:

=IF(B7="null","",IF(B7>0,"Purchase",""))

Which means that if you accidentally typed "Null" then you'd still get a blank cell, as the second If statement forces "Purchase" only if the information in B7 can be interpreted as a number (greater than 0).

[jim360]

NJ's suggestion seems the most elegant.

[New Judge]

Which is what my formula avoids, Jim, only without the need for "nested IFs". It will only return "purchase" if B7 contains a number.

[New Judge]

Sorry, Jim, I'm one post behind !!!