• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conditional Format Formula Help needed

J

jason

Member
Running into a bit of a head-scratcher... i've come along way on my own and am wondering if this last step is possible; thus i come and post.


I have a list of enteries (numbers) each day that represents the number of 'clicks' a printer has made (each page printed is a click). so hypothetically, the numbers should always be growing (click counter is not reversible). however, as you all very well know it is not a perfect world and people will make typo's from time to time. so i had the idea of using conditional formatting to help highlight possible typos.


Here's a sample of data for 2 printers:

[pre]
Code: 
78573096	37354290
28762217*	37537362
78830242	37660976
78968375*	37783463
78523526*	79071335*
79320352	38072215*
79396308	38152449
79527335	38232743
79898988	38510548
79990310	38622526
80104550	38746925*!
80294446
80515403	39055884
80628359	39163786*!
80702874
80775809	39332556
80867742	39422762
81183530	39624664
81630759*!	39930031*!
[/pre]
here is where i am so far with the CF formula:

=AND(NOT(ISBLANK(K610)),OR(K610>K611,K610<K609))


this will format a cell if it is either greather than the cell below it or less than the cell above it...

the numbers followed by the astric/star/* symbol are currently formatted to my little hearts desire and so far, these are all correctly formatted.


the last step im trying to achieve (not interested in going the SQL route) is to not highlight the cells followed by a blank cell. I marked these numbers with the ! symbol.
 
Assuming the blank cells are truly blank, and not filled with "" or some other unprintable character (most likely if this data is being imported from outside source), the CF formula would be:

=AND(NOT(ISBLANK(K610)),OR(ISBLANK(K611),K611<K610,K610<K609))
 
thats not working as needed.....


i'll have to look at this again tomorrow


i want to to look at the selected cell and format the cell if it is greater than the cell below it or if it is less than the cell above it. however, the 'hurdle' i'm at is there are some (2 im my example) that have a blank cell (truly blank cells). because of these cells being blank it formats the cell above. but what i would like to do is if the cell below it is blank, to look at the next cell to see if it greater than that cell....


lets say a1 is 10, a2 is blank and a3 is 20.

i want the formula to see if a1 is greater than a2...but if a2 is blank, i want it to see if a1 is greater than a3


does that make better sense? paint a clearer picture?
 
Ah, that makes more sense. I got confused by the * symbols. How's this formula:

=AND(NOT(ISBLANK(K610)),OR(INDEX(K611:K$2000,MATCH(TRUE,INDEX(ISNUMBER(K611:K$2000),0),0))<K610,K610<LOOKUP(9E+99,K$609:K609)))

Now we're using different methods for finding previous and next cells.
 
Hi all,


Please try this as well, assuming data in A2:A20...


Code: 
=IF(ISTEXT(A1)=TRUE,TRUE,AND(A1<=A2,A3>=A2))=FALSE


Or see this file:


http://dl.dropbox.com/u/60644346/CF_Next%20%26%20Previous%20Cells%20Check.xlsx


Regards,


Faseeh
 
Hi Jason ,


I don't think it is so simple ; VBA would give you the most accurate result.


We need to keep track of the LAST VALID VALUE ; initially we start with the first value as valid , provided it is not blank ; thereafter , whenever a value is greater than the LAST VALID VALUE , the current value now becomes the LAST VALID VALUE.


When ever an invalid value is encountered , it is flagged as invalid , and the LAST VALID VALUE is not updated.


Thus blanks are easily ignored , since even if they are flagged as invalid , they do not update the LAST VALID VALUE.


Now we come to the issue of comparing any value with both the LAST VALID VALUE and the next value ; this is somewhat difficult to program , since if the next value is on the next step decided to be invalid , then it should anyway not have been used for comparison purposes.


More accurate is to provide for a tolerance ; how much greater can the next value be , compared to the earlier value ; if the norm is to print 250 pages in a day , we can safely say that 25000 is an abnormal figure ; if such an abnormal figure appears as the second or third value in the series , then it can throw the entire check off course.


We can take the median values as the norm , safely ignoring outliers on the lower and upper ends ; this will then give us the valid daily average.


Of course , you will have to decide whether you need this level of accuracy or sophistication.


Narayan
 
You must log in or register to reply here.
Back
Top