Main Area and Open Discussion > General Software Discussion

Test cell value for BLANK in Excel and take action (or not) based on it

(1/2) > >>

questorfla:
This is actually pretty simple. 
Columns Cx and Dx both have an auto calculation function that I need to lock to protect the formula from being changed.
The calculation is based on the contents of column Ax and column Bx. 
The problem is that due to auto calculating, the values of column Cx and Dx are instantly filled in based on calculating a value from BLANK in both Ax and Bx

I need to add a test both C and D calculations that check for Blank values in either Ax or Bx and do nothing if either are blank.
Then Cx and Dx will also be blank. 
As it is, the auto calculation runs and puts a value into Cx and Dx based on a value of BLANK in Ax and Bx.  It is just a nuisance but one that is annoying.
I need to find a way to incorporate this into my current setup such that it will allow the current formulas to run if those cells are filed in with anything other than just blank

 

Target:
there are probably dozens of ways to do this, but without knowing more about your particular case you can try this (obviously this is for cell Cx)

=if(aX = "","",XXXX)

IainB:
there are probably dozens of ways to do this, but without knowing more about your particular case you can try this (obviously this is for cell Cx)
=if(aX = "","",XXXX)
______________________
-Target (September 03, 2015, 12:35 AM)
--- End quote ---
Yes, but bear in mind that:

* "" = NULL (which is neither a text character nor a numeric value).
* " " = SPACE (which is a text character).
* 0 = a numeric value of zero.
You could also consider using this (from Excel Help): the ISBLANK function - Returns TRUE if the value is blank.

TaoPhoenix:
there are probably dozens of ways to do this, but without knowing more about your particular case you can try this (obviously this is for cell Cx)
=if(aX = "","",XXXX)
______________________
-Target (September 03, 2015, 12:35 AM)
--- End quote ---
Yes, but bear in mind that:

* "" = NULL (which is neither a text character nor a numeric value.
* " " = SPACE (which is a text character).
* 0 = a numeric value of zero.
You could also consider using this (from Excel Help): the ISBLANK function - Returns TRUE if the value is blank.
-IainB (September 05, 2015, 06:57 AM)
--- End quote ---

Actually Iain you could be on to a nice little tip for him because Nulls and ISBLANK could fail out if he somehow hit space and it got in there. So he could write it as a compound effect to check for two or three of those cases.

IainB:
Actually Iain you could be on to a nice little tip for him because Nulls and ISBLANK could fail out if he somehow hit space and it got in there. So he could write it as a compound effect to check for two or three of those cases.
_________________
-TaoPhoenix (September 05, 2015, 07:54 AM)
--- End quote ---
Yes, I realise that is a possibility, however, as @Target put it:
...but without knowing more about your particular case...
_________________
-Target (September 03, 2015, 12:35 AM)
--- End quote ---

The issue as I see it is data quality, - i.e., not so much that there is this apparent "problem" that you need to check for something (whilst performing a calculation) in the spreadsheet data that has the potential to mess up your calculations at that or a later stage,  but that the "something" could actually be there in the first place.

When processing data, the best practice approach would typically be to validate the data and clean it up at the outset - before you start to process it - so that the possibility of having invalid data during processing is eliminated. I'm not sure whether data quality is being considered in this particular case though.

Navigation

[0] Message Index

[#] Next page