Home | Blog | Software | Reviews and Features | Forum | Help | Donate | About us
topbanner_forum
  *

avatar image

Welcome, Guest. Please login or register.
Did you miss your activation email?

Login with username, password and session length
  • December 06, 2016, 03:50:09 PM
  • Proudly celebrating 10 years online.
  • Donate now to become a lifetime supporting member of the site and get a non-expiring license key for all of our programs.
  • donate

Author Topic: Test cell value for BLANK in Excel and take action (or not) based on it  (Read 1172 times)

questorfla

  • Supporting Member
  • Joined in 2012
  • **
  • Posts: 510
  • Fighting Slime all the Time
    • View Profile
    • Donate to Member
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

  • Honorary Member
  • Joined in 2006
  • **
  • Posts: 1,605
    • View Profile
    • Donate to Member
Re: Test cell value for BLANK in Excel and take action (or not) based on it
« Reply #1 on: September 03, 2015, 12:35:06 AM »
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

  • Supporting Member
  • Joined in 2008
  • **
  • Posts: 6,139
  • Slartibartfarst
    • View Profile
    • Donate to Member
Re: Test cell value for BLANK in Excel and take action (or not) based on it
« Reply #2 on: September 05, 2015, 06:57:12 AM »
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)
______________________
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.
« Last Edit: September 06, 2015, 03:43:11 AM by IainB »

TaoPhoenix

  • Supporting Member
  • Joined in 2011
  • **
  • Posts: 4,550
    • View Profile
    • Donate to Member
Re: Test cell value for BLANK in Excel and take action (or not) based on it
« Reply #3 on: September 05, 2015, 07:54:02 AM »
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)
______________________
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.

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

  • Supporting Member
  • Joined in 2008
  • **
  • Posts: 6,139
  • Slartibartfarst
    • View Profile
    • Donate to Member
Re: Test cell value for BLANK in Excel and take action (or not) based on it
« Reply #4 on: September 06, 2015, 03:41:45 AM »
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.
_________________
Yes, I realise that is a possibility, however, as @Target put it:
...but without knowing more about your particular case...
_________________

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.

questorfla

  • Supporting Member
  • Joined in 2012
  • **
  • Posts: 510
  • Fighting Slime all the Time
    • View Profile
    • Donate to Member
Re: Test cell value for BLANK in Excel and take action (or not) based on it
« Reply #5 on: September 09, 2015, 11:03:06 PM »
C3=(LEFT(md5hash(A3&B3),8))

OK maybe this will clear the playing field.

if A or B is left blank this formula auto-fills in the value of C to be used in further calculations.  If both A and B are empty the sheet fills itself in
showing the returned value is d41d8cd9 which is a waste and just confuses everyone
I would rather C stayed Blank until a value of some kind is put in both A and B.

since this is a a Hash calculation both A and B are normally alpha values but an empty sheet.. not sure if it counts it as blank or ??
"nothing"?  either way, all i am trying to accomplish is preventing the calculation from running if the A or B cells are empty.
To work correctly, both A and B have to be a "real" value of some kind and if either one is empty it means somebody didn't fill in the sheet correctly
So I would prefer it check for either (A  OR  B)  not (just A) or (just B)

I an mot that good at Excel and wish i could just state the above in plain English :)


Target

  • Honorary Member
  • Joined in 2006
  • **
  • Posts: 1,605
    • View Profile
    • Donate to Member
Re: Test cell value for BLANK in Excel and take action (or not) based on it
« Reply #6 on: September 10, 2015, 12:00:54 AM »
=if(trim(a3&B3) = "","",XXXX)?

this will check both Ax AND Bx for a value, and return nothing if true, or XXXX if false

still too many variables for me to say with any certainty (I can still think of several possible scenarios/solutions).

Quote
To work correctly, both A and B have to be a "real" value of some kind and if either one is empty it means somebody didn't fill in the sheet correctly

this infers some sort of some sort of data validation rules could be applied which might solve some (if not all) of your data quality issues.

questorfla

  • Supporting Member
  • Joined in 2012
  • **
  • Posts: 510
  • Fighting Slime all the Time
    • View Profile
    • Donate to Member
Re: Test cell value for BLANK in Excel and take action (or not) based on it
« Reply #7 on: September 10, 2015, 09:30:36 AM »
Thanks Target >>                       (note: see end of this before proceeding)

But the values used for A and B really COULD be almost anything as long as:
1.  It MUST not be blank and
2:  Each set of Ax  Bx  must be unique in the group of 30 entries.

I hate to admit but i am still a little confused about how to use this in the formula presented to either stop the calculations of preceding columns or at the very least end up filling in the C and all other calculations with a BLANK if either A or B are BLANK.  It seems that it would still continue just using the value returned of 'XXXX' if both were blank. 

The statement i am looking for would be one to completely stop the calculation if either A or B are blank.  Would not returning 'XXXX' in that case simply plug right in to the next step as a value and create a result which would go into C and the sheet would move on to the next row and so on.  Since the values are Auto-calculated, nothing stops them from proceeding to the end of the sheet.  The statement I need is one that says if they are blank then put BLANK into C so the entire sheet stays blank until a valid entry is input into both A and B. 

Or else a way to prevent the "auto-calculation" from proceeding at all 

To keep things simple i did not go into the who thing because if i could find a test that would work, i col use it everywhere this occurs but there are 8 more columns using similar auto calculations to assign additional codes on the same row.  If i don't stop it before it starts, i end up with a sheet full of numbers that all are created from the initial use of (BLANK) for either Ax or Bx  ll the way from A1 B1  to  A30 B30

If I had known how to prevent anything from happening if either A or B were blank by using a (test) on A  and a (test) on B that would simply stop the precession that would make more sense but again, i don't know the method. 

The results obtained could be valid for what is needed as long as they put something in A and B and as long as they do not use the exact same input for A and B.    But that part I can legitimately say is out of my control, the user is supposed to ensure that this is never the case.

The intent is to create a set of semi-random 8 digit "codewords" that can be recreated if needed  as long as the same inputs are used.  And the only rules involved is that both A and B must have a "non-empty" and non-identical value.  I cannot control the non-identical (or probably could if i wanted to really get into it) but it seems i should be able to control it such that i can hand them a BLANK spreadsheet to start with which , at this point, is impossible.

This was the best I could come up with on the spot to make the code with a specific formula which could easily be modified for subsequent groups and unless someone knew what formula was used they would not have a clue as what codes went with which entries      I hope :) .
That is why the codes are hidden and auto-calculated.  Only the person tho sets up the initial "rules" for those calculations would know how to recreate it.

There are probably better ways to do this but it has now become a challenge (to me) to find a way to not end up with a sheet full of the same repetitions of codes all based on A and B being blank.  If A and B were both XXXX it would still make a sheet full of hash codes.
I am currently telling them to ignore the lines where there is nothing in A or B but Murphy's Law guarantees that sooner or later someone will assign those values created from BLANK A and BLANK B to something.  And truth be known it is such a glaring deficiency i cannot believe there isn't a simple way to prevent it from happening.  The only entries made by the user of the sheet are to put values in A and B and ensure there are no duplicates in the set of 30 or less.

It is just annoying to me that i cannot prevent that error.

PS:  :)  PLEASE excuse my ignorance and if your reply would in truth give me BLANK cells for all calculations using that test, I apologize and it means I am simply not using it as you intended.  As i said, Excel is not my forte'. :huh:
« Last Edit: September 10, 2015, 10:24:11 AM by questorfla »

Target

  • Honorary Member
  • Joined in 2006
  • **
  • Posts: 1,605
    • View Profile
    • Donate to Member
Re: Test cell value for BLANK in Excel and take action (or not) based on it
« Reply #8 on: September 11, 2015, 01:38:41 AM »
OK, let see what we can do here....

the simple answer is to substitute the formulas in columns C and D for the XXXX so the formula now becomes

   C3=if(trim(a3&B3) = "","",(LEFT(md5hash(A3&B3),8))

of course this doesn't cater for the situation where the (damn!!) user enters the same value twice, so

   C3=if(trim(a3&B3) = "","",if(trim(A3) = trim(B3), "GODAMMIT, I TOLD YOU NOT TO PUT THE SAME VALUE IN BOTH FIELDS, NOW GO BACK AND DO IT PROPERLY",(LEFT(md5hash(A3&B3),8))

this will test if the fields match (and prompt the user), or are empty, and do nothing).  Match this up with some conditional formatting and you've got a pretty good flag

Of course as IanB pointed out data quality is a key consideration, eg what are the criteria for generating the values in Ax and Bx?  Do they have to be numeric, or alpha, or alphanumeric?  Do they have to be a certain length, or within a set length (ie 6 to 8 chars), and so on.  Answering those questions in as much detail as possible will enable you too better refine the rulesets
« Last Edit: September 11, 2015, 07:23:21 AM by Target »