Conditional Formatting Question

[David Fodden]

Hello everyone

I'm using Excel 97 and have set up some Conditional Formatting. The
Conditional Format in H6 is a formula: =((ABS(H6-G6))>1). It is possible
that there may be no value recorded in G6 so is it possible to have the
formula look at the previous value (even if it is in C6 and D6:G6 are empty)
for the comparison?

Thanks for your time.

David

 

[dvt]

Hello everyone

I'm using Excel 97 and have set up some Conditional Formatting. The
Conditional Format in H6 is a formula: =((ABS(H6-G6))>1). It is
possible that there may be no value recorded in G6 so is it possible
to have the formula look at the previous value (even if it is in C6
and D6:G6 are empty) for the comparison?

A brute force method: nested IF statements. In case noone suggests a
prettier method, here is the fundamental idea.

=IF(G6>0, ABS(H6-G6)>1, IF(F6>0, ABS(H6-F6)>1, IF(E6>0,
ABS(H6-E6)>1,ABS(H6)>1)))

That tests G6, F6, and E6 in that order and uses the first cell that is
greater than zero. If all three cells are 0 or less, the result is
ABS(H6)>1. You may wish to change the condition (i.e. G6<>0), you might
wish to add further nested IFs, and change the result when E6:G6 are all
empty.

Dave
dvt at psu dot edu

 

[J.E. McGimpsey]

One way:

=ABS(H6-IF(ISBLANK(G6),IF(ISBLANK(F6),IF(ISBLANK(E6),IF(ISBLANK(D6),
C6,D6),E6),F6),G6))>1

 

[David Fodden]

Thanks for the suggestions but there may be a whole string of blank cells,
perhaps D6:Q6 so I would need to have the formatting in R6 that compares R6
with C6!

Does any guru have any other ideas that I could try?

Thanks for your patience and time.

David

 

[dvt]

Does any guru have any other ideas that I could try?

Data in C4:C15, conditional formatting on C20, C1:C2 blank, C3 as header
row, and C16:C19 blank. The conditional formatting is applied if the last
value in the range is > 20 (your condition is different). Apply this
formula to the conditional cell formatting in C20:
=OFFSET($C$4,COUNTA(C1:C19)-2,0)>20

Dave
dvt at psu dot edu

 

[David Fodden]

Thank you - I suspected that I'd have to use OFFSET but didn't know how!
I'll see how I get along.

David

 

[David Fodden]

Hi

I've looked at the procedure that you've suggested and can't get it to work
properly. It counts empty cells within the block of filled cells and counts
these too. An example of the data is given below:

A1: 1
A2: 8
A3: <blank>
A4: 4
A5: <blank>
A6: 12
A7: 9
A8: <blank>
A9: <blank>
A10: 15

The question is "what formatting do I have to put into A10 to compare A10
with the last cell that contains data, i.e. A7". The problem arises because
A7 may also be blank so I'd want to compare A10 with A6. The list will also
extend beyond A10!

I suspect that a combination of COUNT and OFFSET will be needed -
essentially, I need to know how to determine the last filled cell before the
cell that has the conditional formatting.

I hope this further explanation will allow someone to come up with a "trick"
that works!

David

 

[Immanuel]

Hi David,

Assuming that your table of values starts at C3 and ends at Z65536, select
cells D4:Z65536 and enter the following formula in the conditional
formatting (formula) text box:

[=IF((NOT(ISBLANK(D3)))*(MAX(IF($C3:C3="",0,COLUMN($C3:C3)))),IF(ABS(INDEX($
A3:$Z3,,MAX(IF($C3:C3="",3,COLUMN($C3:C3))))-D3)>1,TRUE,FALSE),FALSE)]

The formula assumes that you only want to apply formatting to non-blank
cells.

/i.